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,
How do I show the result set in a 'Multi-row card of only 0 values from the Custom Column?
Sample data is provided below:
- Difference is a Custom Column which I created in the Power Query Editor [Example 'Diff' shown in above image]
"Column 2 - Column 1"
- Next, I have created a MEASURE to place them in the Multi-row card.
Measure = CALCULATE(COUNTROWS(FILTER('Table1', 'Table1'[Diff]=0)))
This is when I get an calculation error in measure. Do I need to use some other expression?
Appreciate your response. Thanks!
Solved! Go to Solution.
Seems to me that DIFF column is formatted as text.
Try this
Measure = CALCULATE ( COUNTROWS ( FILTER ( 'Table1', VALUE ( 'Table1'[Diff] ) = 0 ) ) )
Hi @BBIUser
You may try to change the type of Diff column to 'Whole Number'. Then your measure will not get the error.
Regards,
Cherie
hello, as I'm running into this same error, I'd like to see if someone can help: my columns are all date/time-formatted columns, and I still get the error. I suspect it has to do with the fact that there are empty cells which show "null" in Power Query. Can it be that PBI does not function to calculate the difference between two dates in case there are empty cells?? and if so, how would you suggest to fix it? thanks a lot!
I am returning the same error message with my code and am not sure which columns are not in the correct format...
I am trying to fill empty rows with entries later on.
Hi @BBIUser
You may try to change the type of Diff column to 'Whole Number'. Then your measure will not get the error.
Regards,
Cherie
I was trying to change the Measure's data type to Whole number instead of the Custom Column. Changing the Custom Column to Whole Number data type with my original DAX worked for me as suggested by Cherie.
Thanks for the Solutions @v-cherch-msft , @Zubair_Muhammad and @Anonymous.
Does anyone know how to find the DAX error when you have formatted every single data table and combined data table to ensure there will be no format errors and there are no warning symbols on any files? I hit the error when trying to add a new measure to a visual. I am basically reproducing the same report I have already done but I need to change the number of reports so I started over and now I can't get any of the equations to work without the DAX format error. Thanks
Did you figure this out? I am running into the same issue. All my look up values are text but I'm getting and error message about mixed types.
I had a sum on one of my measures that after I changed it not to summarize I did not get this error anymore. That probably doesn't help. I also had the same sum in a prior version and I didn't get this error so I am not sure what exactly fixed it. I did have a mix of numbers and text, I checked every single table again to see if I could find one that had multiple formats and there were none.
Try:
Measure = CALCULATE( COUNTROWS( 'Table1'[Diff] ) , FILTER('Table1', 'Table1'[Diff]=0 ) )
CALCULATE( What to Calculate , Filter or other expression )
Thanks @Anonymous for the reply.
I did try this expression before posting here with slight modification in it and I was getting the same error . I dint place ('Table1'[Diff]) , the coulmn since I had used the Custom Column which is not showing while creating this expression and shows only the Table Name for COUNTROWS.
Measure = CALCULATE( COUNTROWS( 'Table1') , FILTER('Table1', 'Table1'[Diff]=0 ) )
Seems to me that DIFF column is formatted as text.
Try this
Measure = CALCULATE ( COUNTROWS ( FILTER ( 'Table1', VALUE ( 'Table1'[Diff] ) = 0 ) ) )
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |