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 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
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 |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |