Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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
benfedit's avatar
benfedit
Advocate I
4 years ago

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's avatar
    bcdobbs
    Community Champion
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

       

      All my domains that don't have any laptops are just not showing up in the Domain column.  

       

      I want it to show the domain and then  0, 0, No Laptops

       

      Here is what it looks like so far in Power BI:

       

       

       

      And the fields I have available from my .CSV import: