Forum Discussion
IF and COUNTIFS Excel Syntax to DAX - need help making calculations using DAX
Hello everyone!
I am trying to take a concept from an Excel Spreadsheet I have and recreate that in a Power BI matrix report.
The reason I don't just use the Spreadsheet as my data source is that I want to export straight .CSV files from a product I am working with and update that regularly by exporting the latest version of the .CSV file.
So,
I have the following table in Excel I'm trying to re-create in Power BI:
The formula behind the numbers in the "Not Encrypted" column:
=COUNTIFS(EPMTable[Encryption Method],"0 - None",EPMTable[Domain],A3,EPMTable[Platform],"Laptop")
And then the "Encrypted" column:
=COUNTIFS(EPMTable[Encryption Method],"<>0 - None",EPMTable[Domain],A3,EPMTable[Platform],"Laptop")
And the % Encrypted column:
=IF(SUM(C3+B3)=0,"No Laptops",SUM(C3/(SUM(C3+B3)))
This is pulling from a sheet like this:
I have exported the data to a .CSV file I have been working with to create some Matrix reports in Power BI.
I am pretty new to Power BI, I've gone through most of a course but still chugging through the DAX stuff and so forth.
However I have a deadline for a customer I need to meet today, so I am reaching out to this creative bunch.
Measures for each column:
No Encryption Laptops = CALCULATE ( COUNTROWS ( EPMTable ), EPMTable[Encryption Method] = "0 - None", EPMTable[Platform] = "Laptop" ) Encrypted Laptops = CALCULATE ( COUNTROWS ( EPMTable ), EPMTable[Encryption Method] <> "0 - None", EPMTable[Platform] = "Laptop" ) Percentage Encrypted = VAR Laptops = CALCULATE ( COUNTROWS (EPMTable), EPMTable[Platform] = "Laptop" ) VAR PercEncypted = DIVIDE ( [Encrypted Laptops], Laptops ) RETURN PercEncypted
10 Replies
- bcdobbs
Community Champion
The syntax you need to replace your countifs is something along these lines:
No Encryption = CALCULATE ( COUNTROWS ( EPMTable ), EPMTable[Encryption Method] = "0 - None" )- bcdobbs
Measures for each column:
No Encryption Laptops = CALCULATE ( COUNTROWS ( EPMTable ), EPMTable[Encryption Method] = "0 - None", EPMTable[Platform] = "Laptop" ) Encrypted Laptops = CALCULATE ( COUNTROWS ( EPMTable ), EPMTable[Encryption Method] <> "0 - None", EPMTable[Platform] = "Laptop" ) Percentage Encrypted = VAR Laptops = CALCULATE ( COUNTROWS (EPMTable), EPMTable[Platform] = "Laptop" ) VAR PercEncypted = DIVIDE ( [Encrypted Laptops], Laptops ) RETURN PercEncypted
benfeditAdvocate I
hi Anonymous,
If you always have just a few values to check in the IF condition you can write something like this:
calculate(countrows(yourtable),
EPMTable[Encryption Method]="0 - None" || EPMTable[Platform],"Laptop")If you need to reproduce what you did in Excel with a dynamic value for "A3" you will need to force your slicer to always have a single value or to use the firstnonblank function.
- bcdobbs
I assumed A3 was just the domain name in the report. In which case putting the domain names on the rows of a matrix will take care of that aspect.