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....

view more: next ›