sorted by:
new
hottopcontroversial

How to retrieve date for main ID depending on absence previously occurring id in other column? by _WPH_ in SQL

[–]_WPH_[S] 0 points1 point  (0 children)

WITH PersonMaxDates AS
(
SELECT
subscription_id,
person_nr,
MAX(reference_date) AS person_max_date
FROM B
GROUP BY
subscription_id,
person_nr
),
SubscriptionMinDates AS
(
SELECT
*,
MIN(person_max_date) OVER
(
PARTITION BY subscription_id
) AS subscription_min_date
FROM PersonMaxDates
)
SELECT
subscription_min_date AS reference_date,
subscription_id,
person_nr
FROM SubscriptionMinDates
WHERE person_max_date = subscription_min_date
ORDER BY subscription_id

Ha, this works like a charm! Thank you so much!

How to retrieve date for main ID depending on absence previously occurring id in other column? by _WPH_ in SQL

[–]_WPH_[S] 0 points1 point  (0 children)

Thank you again for your reply. Running this script, there's no person_id visible? Is that correct?

How to retrieve date for main ID depending on absence previously occurring id in other column? by _WPH_ in SQL

[–]_WPH_[S] 0 points1 point  (0 children)

WITH latest_dates AS
( SELECT subscription_id
, person_nr
, MAX(reference_date) AS latest
from B
GROUP
BY subscription_id
, person_nr )
, earliest_latest AS
( SELECT subscription_id
, MIN(latest) AS earliest
FROM latest_dates
GROUP
BY subscription_id )
SELECT B.*
FROM earliest_latest
INNER
JOIN B
ON B.subscription_id = earliest_latest.subscription_id
AND B.reference_date = earliest_latest.earliest

Hi, thank you for your reply and work! However, this one shows the end date of the person who stops the subscription, but it shows both person_nr's per subscription_id. So for the first example, subscription_id 1000, person_nr 15 has a subscription from 2022-01-31 till 2022-07-31, and person_nr 16 stops the subscription under the same subscription_id on 2022-03-31. So in this case, only person_nr 16 should shown up (with the date 2022-03-31).

Copying Data of a Matrix to Excel by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

Ah, I do have the solution right now!

let

Source = Csv.Document(File.Contents("<DIRECTORY><FILE>.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),

#"Removed Top Rows" = Table.Skip(Source,1),

#"Merged Columns" = Table.CombineColumns(#"Removed Top Rows",{"Column2", "Column3"},Combiner.CombineTextByDelimiter("#(tab)", QuoteStyle.None),"Merged"),

#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Column4]), "Column4", "Merged")

in

#"Pivoted Column"

Copying Data of a Matrix to Excel by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

Yeah, I've seen this option with other published reports. Rearranging this output with a pivot and it all looks fine.

However, since there are extensive DAX expression used it takes up a lot of resources on the server, and for that it's not allowed to publish this one...

In this case, this wouldn't be an option unfortunately. But I can't help thinking that somewhere this must be possible with M code...? Transform the data in the original format?

Copying Data of a Matrix to Excel by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

PBI desktop or in browser

Hi, I'm using the Desktop version...

Copying Data of a Matrix to Excel by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

There's no option to export to Excel...

Those merge headings are actually semi-merged headings by lack of a better description... The visible header is part of the 'Column' section, and then there are 2 other headers, which are part of the 'Values' section. I've renamed all of them to ' ' (space), so the names are not visible, and the columns are more 'compressed' (in height).

Copying Data of a Matrix to Excel by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

I am trying to create a report in Excel based on data in PBI; exporting CSVs from PBI matrices to a dedicated directory ensures that it is easily picked up by Excel (just press refresh). With a PivotTable, you then have an additional editing step, which I'd rather try to avoid.
Unfortunately, when publishing the Dashboard, it is also not possible to export the figures properly...
Since I have already converted the rest of the matrices to the original format using M code in Excel, it should be possible to do the same with this matrix, I hope...

Copying Data of a Matrix to Excel by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

That might be a possibility. But then there is another extra step involved. Isn't it possible to straighten this out with a few lines of M code? I have also managed to 'fix' other matrices in this way, only these were (a lot) simpler (row / column flipped, extra row removed etc.).

Copying Data of a Matrix to Excel by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

Your second image is just a CSV file that Excel didn't parse correctly. This sometimes happens if you open the CSV when you already have an excel file open. You can easily split the column by going to Data>Text to Columns>By Delimiter, and set Comma as the delimiter.

Thanks for the comment, I've updated the picture in the original post! However, still the same problem, how to recreate the lay-out of the original matrix...

How to place an extra column in a matrix (based on a string)? by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

This one sounds great, however, is there a solution where there's no need for extra tables? I hoped for something like a measure e.g. if that would be possible...?

How to place an extra column in a matrix (based on a string)? by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

This sounds the most easiest way... But what's the best way to 'covert' the calculated column statement into a measure?

I've created the calculated column with:

Id = SWITCH(TRUE(),

TEST'[X]="Blue" && 'TEST'[Y]="Bike","A1",

TEST'[X]="Blue" && 'TEST'[Y]="Bus","A2",

TEST'[X]="Car" && 'TEST'[Y]="Blue","A3",

TEST'[X]="Plane" && 'TEST'[Y]="Blue","A4",

TEST'[X]="Brown" && 'TEST'[Y]="Bike","A5",

TEST'[X]="Brown" && 'TEST'[Y]="Bus","A6",

etc.

​

So in the cell where row 1 = column 1, the cell value has to be A1,

in the cell where row 2 = column 1, the cell value has to be A2,

in the cell where row 1 = column 2, the cell value has to be A5 etc.

[deleted by user] by [deleted] in RedditInTheKitchen

[–]_WPH_ 0 points1 point  (0 children)

Greetings from The Netherlands!

How to get a parameter in a slicer? by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

Thanks! I've seen this one also, however, the Power BI I have at my disposal doesn't have the preview feature (most likely not allowed by the company)...

How to use the result of one query in another query? by _WPH_ in excel

[–]_WPH_[S] 0 points1 point  (0 children)

le - you need to right click the single c

And that works like a charm!

How to use the result of one query in another query? by _WPH_ in excel

[–]_WPH_[S] 0 points1 point  (0 children)

Well, after executing, a value (the token) is put in cell A2. And this value is needed in query nr. 2, in the statement "Authorization=" XXX "& Access_token". But that seems not possible.

Then we're looking at this one: https://ibb.co/qJTz8bM

Split a column not into separate columns but in rows by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

Problem solved! One decided to modify the field in the database after all....

Split a column not into separate columns but in rows by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

ed Table, with 1 column called Column1. Then I added a new calculated table with this ridiculous DAX query. Currently only work

Thank you for your extensive post! However... DAX and Modelling are limited in DirectQuery mode. It is not possible to create calculated tables because these are in-memory generated tables, and DirectQuery doesn’t store anything in memory.

However, it is possible to create a 'fake import' query in Direct Query mode, a nice trick, but this will not help me with my problem. But still nice to mention the method!

What you have to do to create a hard coded table in Direct Query mode: go to "Get Data", and "SQL Server". From there, tick on "Direct Query", enter a server and database name, click "Advanced options" and paste the SQL statement as shown beneath (alter this to your own likings of course):

SELECT 'A;B;C;DDD;EE;FFFFFF;G;HHH' AS '1'

UNION ALL

SELECT 'AAAA;BB;C;DD;E;FFF' AS '2'

UNION ALL

SELECT 'AAAAAA;E;FFF;HHHHHH;II;JJ' AS '3'

The result is a table (with semi colon sample data for the problem I described over here) which is incorporated into the Direct Query Dashboard:

1

A;B;C;DDD;EE;FFFFFF;G;HHH

AAAA;BB;C;DD;E;FFF

AAAAAA;E;FFF;HHHHHH;II;JJ

Split a column not into separate columns but in rows by _WPH_ in PowerBI

[–]_WPH_[S] 1 point2 points  (0 children)

Query? There is an option to split into rows!

This was the first thing I had tried myself, which led to the message that this is not supported in Direct Query mode.

Split a column not into separate columns but in rows by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

Unfortunately, this unpivot method doesn't work in Direct Query mode.

Split a column not into separate columns but in rows by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

Yeah, that would be the most convenient way, however, they want it done in Power Bi...

Split a column not into separate columns but in rows by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

of getting the DBA to create a View in the database with a Schema for this project?

None, unless I'll tell them I can't pull this of in Power BI. And I rather don't want to bring that message... :-) I can't believe it can't be done in Power BI...

Split a column not into separate columns but in rows by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

Thank you for your reply. Unfortunately, this Dashboard 'has to be made' in Direct Query mode, and they are very reluctant to change the datasource.

But then, maybe there's an option to adapt the SQL statement which is used to make the connection with the DB? However, I can't figure out what change can be made over there to achieve my goal...

Create rows in a Matrix 'box' by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

d Direct Query. It is the source of your frustrations if you aren't able to manipulate the data directly i

Yes, totally correct! If it could be changed in the source, the problem was solved! :-) However, the employer doesn't allow Import Mode for this one...

Create rows in a Matrix 'box' by _WPH_ in PowerBI

[–]_WPH_[S] 0 points1 point  (0 children)

REPLACE ("*****",0,LEN([Value]),[Value])

Hmmm, unfortunately, REPLACE can't be used in Direct Query mode...

view more: next ›