Forum Discussion
Helper IMeasure filter on two différents tables
Hello,
I want to create a cumulative measure where I need to filter in two different tables.
I have a fact table and a calendar table. My two tables are linked in Power Bi.
I have this representation : It's cumulative sum of my quantity A and quanttiy B. After i calculate an difference between cumulative A and cumulative B.
I want to count the number of items over a period only when the quantity is different from zero.
The quantity filter does not work in my DAX formula and i don't know why ?
Cumulative =
var __currdate = max(calendar[Date])
var cumulative = calculate(Distinctcount(fact_table[items]), filter(All(calendar),calendar[Date]<=__currdate && Difference QtyA & Qty B <>0
Can explain me why this dax formula doesn't work, please.
You can download pbix with data here :
https://1drv.ms/u/s!AjUN6-w6YnuGz1xaS6JepuupW_Q_?e=6coXNT
Thanks
PBI file attached.
Hope this helps.
7 Replies
- amitchandak
Super User
LLJ1221 , Try two measure like given below
M1 = calculate(Distinctcount(fact_table[items]),filter(fact_table, quantity <>0) )
Cumulative =
calculate([M1], filter(All(calendar),calendar[Date]<=max(calendar[Date])))
LLJ1221Hello amitchandak,
Thanks you for your answer. I do this two dax measure.
And i obtain items with quantity <>0 but the total resultat is false.
Power BI count all item i have in my period instead (i verify and i have 29 items) of to count only items with quantity different of zero.
The result in Power BI :
- Ashish_Mathur
Hi,
Your question is not clear. Share the input table and the result table.
- LLJ1221
Hi Ashish_Mathur,
You can download pbix with my data here :
https://1drv.ms/u/s!AjUN6-w6YnuGz1xaS6JepuupW_Q_?e=6coXNT
my problem : I want to count distinct numbers of items where quantity <>0 but my formula Dax doesn't work
- Ashish_Mathur
PBI file attached.
Hope this helps.
