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 Power Community!
so this shows the "known" data and "unknown" data (data with no delivery date or forwarder). So far so good.
Now I have a date slicer. If you use the date slicer it for the last months it only shows the known data.
The reason is that the unknown data has of course no date but my boss wants to know how much unknown data we have every month not only overall....
Thank you for your time.
Solved! Go to Solution.
Hi @Anonymous ,
According to my understanding, you want to display total/known/unknown data based on date slicer, right?
You could try this:
Total = COUNTROWS('Table')
Known Data =
COUNTROWS (
FILTER (
'Table',
'Table'[DeliveryDate] <> BLANK ()
&& 'Table'[Forwarder] <> BLANK ()
)
) + 0
The final output looks like this:
Please kindly check my pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to my understanding, you want to display total/known/unknown data based on date slicer, right?
You could try this:
Total = COUNTROWS('Table')
Known Data =
COUNTROWS (
FILTER (
'Table',
'Table'[DeliveryDate] <> BLANK ()
&& 'Table'[Forwarder] <> BLANK ()
)
) + 0
The final output looks like this:
Please kindly check my pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-eqin-msft Excuse me. I had no time in the holiday season...
Thank you for your time - it worked perfectly 😊
@Anonymous ,
for unknown data use like
unknown new = calculate([unknown], all(Date), isblank(Table[Deilvery Date]))
or
unknown new = calculate([unknown], all(Table), isblank(Table[Deilvery Date]))
or
unknown new = calculate([unknown], filter(all(Table[Date]), isblank(Table[Deilvery Date])))
@amitchandak Thanks for the help.
now the number of unknown data is constant but it still does not change with the date slicer. Do you have an idea?
@Anonymous You need to have a date column for reference. What is your data source? Is there a created or modified date we can use? You may need to use the DAX function USERELATIONSHIP() to use an inactive relationship between date table and modified date to know when the unknown data was modified or created??
Hopefully that makes some sense?
Without some date, we can't know which numbers apply to each month.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |