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.
Hello and Happy Friday!
I am trying to recreate a Excel report in Power BI and I am almost there but I am having trouble with 2 calculations and how to convert the syntax that is in Excel over to Power BI DAX.
The Excel report uses a COUNTIF vs something like -
Suggestions?
Solved! Go to Solution.
Hi @Anonymous
You may try to use FIND function to get the two columns.For example:
Column = CALCULATE ( COUNTROWS ( 'Sample' ), FILTER ( ALLEXCEPT ( 'Sample', 'Sample'[TechName] ), FIND ( "Exists in both GoCanvas and e-automate", 'Sample'[TechMatch], 1, 0 ) > 0 && FIND ( "MAC", 'Sample'[MAC?], 1, 0 ) > 0 && FIND ( "Exist", 'Sample'[MAC?], 1, 0 ) > 0 ) )
Column 2 = CALCULATE ( COUNTROWS ( 'Sample' ), FILTER ( ALLEXCEPT ( 'Sample', 'Sample'[TechName] ), FIND ( "Exists in both GoCanvas and e-automate", 'Sample'[TechMatch], 1, 0 ) > 0 || FIND ( "e-automate Only,not in GoCanvas", 'Sample'[TechMatch], 1, 0 ) > 0 ) )
Regards,
Hi @Anonymous
You may try to use FIND function to get the two columns.For example:
Column = CALCULATE ( COUNTROWS ( 'Sample' ), FILTER ( ALLEXCEPT ( 'Sample', 'Sample'[TechName] ), FIND ( "Exists in both GoCanvas and e-automate", 'Sample'[TechMatch], 1, 0 ) > 0 && FIND ( "MAC", 'Sample'[MAC?], 1, 0 ) > 0 && FIND ( "Exist", 'Sample'[MAC?], 1, 0 ) > 0 ) )
Column 2 = CALCULATE ( COUNTROWS ( 'Sample' ), FILTER ( ALLEXCEPT ( 'Sample', 'Sample'[TechName] ), FIND ( "Exists in both GoCanvas and e-automate", 'Sample'[TechMatch], 1, 0 ) > 0 || FIND ( "e-automate Only,not in GoCanvas", 'Sample'[TechMatch], 1, 0 ) > 0 ) )
Regards,
Here is what I ended up with that seems to get me what I needed.
Thank you so much!
AWESOME!!
One last question, where would I add an ELSE to return 0 so that the field is populated with a value?
Thank you SO MUCH!!
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 |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |