Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys,
I was wondering if somebody could help me. It is concerning a PBI report with 2 visualisations, and I am working with an SSAS tabular model.
Visualisation 1: line graph with date (by day) on the X axis, and amount of files on the Y axis (last 3 years)
Visualisation 2: line graph with months on the X axis, amount of files on the Y axis and years as legend. (last 3 years)
Question:
I wanted to have a "0" instead of "blank" value for visual 1. That way, I can see for each data if there was one or multiple files per day. Therefore, I used the DAX formula "Measure = [amount of files] + 0", which works great for visualisation 1.
However, If I am using this formula for visualisation 2, it shows a line at "0" at november and december 2017. This I do not want to see, because this is a future date with no values.
Is there a DAX formula to make a measure to show a "0" for a blank value for each date earlier than today, but which is showing blank values for future days?
Help is appreciated, because I am not yet that savvy with DAX. Also keep in mind that the DAX formula should not include creating a calculated column, because I want to make the measure on top of an SSAS tabluar model.
Many many thanks in advance!!!
Richard
Solved! Go to Solution.
Hi @RichardV,
You can use IF condition in your measure like:
Measure = IF(MAX(Table[Date].[MonthNo])<=MONTH(TODAY()),[amount of files]+0,BLANK())
Remember to enable 'Show items with no data' on chart axis.
Best regards,
Yuliana Gu
Hi @RichardV,
So, currently, with this formula Measure= IF(MAX('Table'[Date])>DAY(TODAY()) . [Amount of files]+0, BLANK()), you can always achieve the desired result, right?
Regards,
Yuliana Gu
Hi @RichardV,
You can use IF condition in your measure like:
Measure = IF(MAX(Table[Date].[MonthNo])<=MONTH(TODAY()),[amount of files]+0,BLANK())
Remember to enable 'Show items with no data' on chart axis.
Best regards,
Yuliana Gu
Hi @v-yulgu-msft,
Thanks a lot for your comment.
However, it deed not seem to work. Perhaps it has to do with the fact that I don't have the function with "show items with no data' because I create a measure on top of a SSAS tabular model.
The strangest thing is, that I tweaked a little with the formula, and I have seem to make it work. Unfortunately, I do not have an idea why this works.
The only thing that I did, is change "<=" into ">". So the formula becomes:
Measure= IF(MAX('Table'[Date])>DAY(TODAY()) . [Amount of files]+0, BLANK())
I don't have an explanation for why this works. Can I use this safely, or is there any error in this?
Best,
Richard
Hi @RichardV,
So, currently, with this formula Measure= IF(MAX('Table'[Date])>DAY(TODAY()) . [Amount of files]+0, BLANK()), you can always achieve the desired result, right?
Regards,
Yuliana Gu
Yes, although no idea why it works
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |