Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I need help with the following dax. I am creating a table that summerizes the latest dates by id. This works well however I have blank dates. How can I get the latest dates where the id has no blank dates against it?
Solved! Go to Solution.
@Anonymous hi
I have written simple dax similar to your dax except i have applied filter which remove blank dates rows.
It have worked for me.
table2 = SUMMARIZE(FILTER(Table1,Table1[DATE] <> BLANK()),Table1[ID],"MAX DATE",MAX(Table1[DATE]))
Regards,
Novil
If I answer you question, please mark my post as solution.
Id | LastEntryDate |
11 | 23/05/2019 0:00 |
12 | 23/05/2019 0:00 |
13 | 23/05/2019 0:00 |
24 | 24/05/2019 0:00 |
25 | 24/05/2019 0:00 |
26 | |
49 | |
50 | |
51 | 31/05/2019 0:00 |
52 | 8/06/2019 0:00 |
55 | 11/06/2019 0:00 |
@Anonymous hi
I have written simple dax similar to your dax except i have applied filter which remove blank dates rows.
It have worked for me.
table2 = SUMMARIZE(FILTER(Table1,Table1[DATE] <> BLANK()),Table1[ID],"MAX DATE",MAX(Table1[DATE]))
Regards,
Novil
If I answer you question, please mark my post as solution.
Hi
If the other column other than the summerized need also to be Non-Blank for example in our case (Table1[ID]) how can we do that?
Try this-
table2 = SUMMARIZE(FILTER(Table1,Table1[DATE] <> BLANK() && Table1[ID]<>BLANK()),Table1[ID],"MAX DATE",MAX(Table1[DATE]))
Regards,
Novil
If I answer your question, please mark my post as a solution.
Hey,
I use this kind of DAX statement:
Table = FILTER( ADDCOLUMNS( SUMMARIZE( VALUES(Table1[Id]) , Table1[Id] ) , "the latest date" , CALCULATE(LASTNONBLANK('Table1'[LastEntryDate] , MAX('Table1'[LastEntryDate]) )) ) , [the latest date] <> BLANK() )
This creates a table like this:
As you can see from the screenshot from above, there really are no blank rows 🙂
You have to be aware that you have to use TREATAS(...) if you want to use the summarized table as a table filter, as the calculation of the last date breaks the lineage to the column [LastEntryDate] like so:
a measure = var thesummarizedtable = ... return CALCULATE( ... , TREATAS(<thesummarizedtable> , '<the table name>'[Id] , '<the table name>'[LastEntryDate]) )
Hopefully this is what you are looking for.
Regards,
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |