Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys,
I'm using a calculated table to count the number of lines in a table and display it in my goal table with this code :
RESULTATS = SUMMARIZE( FILTER( CROSSJOIN('PRODUCTION_MAINTENANCE';'OBJECTIFS'); --- Join criteria here 'PRODUCTION_MAINTENANCE'[CLIENT]='OBJECTIFS'[CLIENT] && 'PRODUCTION_MAINTENANCE'[OPERATION]='OBJECTIFS'[OPERATION] && 'PRODUCTION_MAINTENANCE'[ETABLISSEMENT]='OBJECTIFS'[TECHNICENTRE] && 'PRODUCTION_MAINTENANCE'[DATE_SORTIE]='OBJECTIFS'[JOUR] ); 'OBJECTIFS'[CLIENT]; 'OBJECTIFS'[OPERATION]; 'OBJECTIFS'[TECHNICENTRE]; 'OBJECTIFS'[JOUR]; 'OBJECTIFS'[NOTIFICATION]; 'OBJECTIFS'[MODULATION]; "PRODUCTION";COUNTROWS('PRODUCTION_MAINTENANCE') )
It works great BUT when i have a date in my goal table ('OBJECTIFS'[JOUR]) and no line the same day in my production table (PRODUCTION_MAINTENANCE[DATE_SORTIE]) the calculated table display nothing instead of a line with my goal and zero in my production calculated column.
Can you help me solve this ? Thanks 🙂
Solved! Go to Solution.
Hi @Anonymous,
You can create a new column instead using the following DAX.
Column = CALCULATE(COUNTROWS(FILTER(CROSSJOIN(OBJECTIFS,PRODUCTION_MAINTENANCE), OBJECTIFS[CLIENT]=PRODUCTION_MAINTENANCE[CLIENT]&&OBJECTIFS[OPERATION]=PRODUCTION_MAINTENANCE[OPERATION]&&OBJECTIFS[TECHNICENTRE]=PRODUCTION_MAINTENANCE[ETABLISSEMENT]&&OBJECTIFS[JOUR]=PRODUCTION_MAINTENANCE[DATE_SORTIE]))+0,ALLSELECTED(OBJECTIFS[CLIENT]))
Thanks,
Lydia Zhang
Maybe you can do this...
... "PRODUCTION";COUNTROWS('PRODUCTION_MAINTENANCE') + 0 )
Thanks MATTBRICE, i've tried but it's not working.
I think that's becase my GOAL table has every 2017's days in it and my PRODUCTION table only the past 2017 days.
Any other idea ? 🙂
Hi @Anonymous,
Directly create a measure in your OBJECTIFS table using the DAX below and check if it returns your expected result.
Measure 2 = COUNTROWS(FILTER(CROSSJOIN(OBJECTIFS,PRODUCTION_MAINTENANCE), OBJECTIFS[CLIENT]=PRODUCTION_MAINTENANCE[CLIENT]&&OBJECTIFS[OPERATION]=PRODUCTION_MAINTENANCE[OPERATION]&&OBJECTIFS[TECHNICENTRE]=PRODUCTION_MAINTENANCE[ETABLISSEMENT]&&OBJECTIFS[JOUR]=PRODUCTION_MAINTENANCE[DATE_SORTIE]))+0
Thanks,
Lydia Zhang
Just tried but because my tables are quite big it seems pretty long to process... Waiting for the result to show up and coming back to you 😉
Ok so it works but it's veeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeery long to process...
Do you think i can add this DAX in a new column of my OBJECTIF table instead of a new measure. It seems that the measure process is calculated each time, so it's very long to process...
Hi @Anonymous,
You can create a new column instead using the following DAX.
Column = CALCULATE(COUNTROWS(FILTER(CROSSJOIN(OBJECTIFS,PRODUCTION_MAINTENANCE), OBJECTIFS[CLIENT]=PRODUCTION_MAINTENANCE[CLIENT]&&OBJECTIFS[OPERATION]=PRODUCTION_MAINTENANCE[OPERATION]&&OBJECTIFS[TECHNICENTRE]=PRODUCTION_MAINTENANCE[ETABLISSEMENT]&&OBJECTIFS[JOUR]=PRODUCTION_MAINTENANCE[DATE_SORTIE]))+0,ALLSELECTED(OBJECTIFS[CLIENT]))
Thanks,
Lydia Zhang
Thanks i'll try it 🙂
What do you think will be the fastest to process for Power BI Online ? Is there a difference ?
Hi @Anonymous,
In your scenario, calculated column is useful than measure, and it processes faster. For the difference between calculated column and measure, please review the similar thread: https://community.powerbi.com/t5/Desktop/column-vs-measure/td-p/13201 .
Thanks,
Lydia Zhang
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |