ERRORs with DAX calculations

[cgd210]

Hi,

I'm trying to finish a PowerBI dashboard, but I can't resolve an error in one of the DAX calculations.

I am revalidating that the frequency with which health check scans are being launched on certain computers is correct.

To do this, I am using two tables:

• Table A, in which I have the list of hostnames and the scanning frequency assigned to each one
• Table B, in which I also have the list of hostnames, and the segment to which they belong (from which it is determined what scanning frequency corresponds to that machine), and if they need automatic (1) or manual (0) scan.

Would be something like this:

Table A

- Column 1: HOSTNAME

- Column 2: SCAN_FRECUENCY

Table B

- Column 1: HOSTNAME

- Column 2: H_SEGMENT

- Column 3: AUTOM_HC_REQUIRED

I created a relation from Table B(1) to Table A(*) through the HOSTNAME columns.

From this information, I create two calculated columns in Table B, with which I translate the name of the segment, by its equivalent frequency, making a distinction between automatic and manual scans:

Table B

- Column 1: HOSTNAME

- Column 2: H_SEGMENT

- Column 3: AUTOM_HC_REQUIRED

- New calculated column 4: Freq_automatic_scan

- New calculated column 5: Freq_manual_scan

where:

Freq_automatic_scan=

IF(LEFT([H_SEGMENT],1)="G",4,

IF(LEFT([H_SEGMENT],1)="P", 12,

IF(LEFT([H_SEGMENT],1)="N", 18,0)))

And

Freq_manual_scan:

IF(LEFT([H_SEGMENT],1)="G",12,

IF(LEFT([H_SEGMENT],1)="P", 18,

IF(LEFT([H_SEGMENT],1)="N", 18,0)))

And then, I created two diferente pages, for filtering only the hostnames with automatic scan in one of them, and only the hostnames with manual scan in the other.

The problem appears when I create another calculated column in Table B to see if the frequency assigned to the machine, according to its segment is greater than, less than or equal to the frequency reported in Table A, thus establishing if it is OK, KO, or scan frequency is too high:

To do this, I créate first a measure out of both tables:

Frequency = SUM(Table A [SCAN_FREQUENCY])

And then, I call this from the new column in Table B:

FREQ CHECK = IF([AUTOM_HC_REQUIRED]="1",

        IF([Freq_automatic_scan]=[ Frequency], "OK",

            IF([Freq_automatic_scan]<[ Frequency],"KO","TOO HIGH FREQ")),

        IF([Freq_manual_scan]=[ Frequency], "OK",

            IF([Freq_manual_scan]<[ Frequency],"KO","TOO HIGH FREQ")))

The result of this operation is that the FREQ CHECK column shows some values that perform the calculation and comparison correctly, and many others that are incomprehensibly incorrect:

For example:

Hostname (Table B)	H_SEGMENT	Frequency	SCAN_FRECUENCY	FREQ CHECK
h1	G	4	4	OK
h2	G	4	4	OK
h3	G	4	4	KO
h4	G	4	4	KO
h5	G	4	4	KO

I'm not seeing the problem and I greatly appreciate any ideas to help me see it. Can you help me?

THanks 🙂

[ValtteriN]

Hi @cgd210 ,

Is there a particular reason you are using so many calculated columns? As a general rule I would do most of the data enriching you are doing here in Power Query. 

After you have the required data, instead of using calculated columns I would use measures to do the validity check.

E.g. let's assume your data would look like this:

For recurring IF sructure instead of using IF(cond1,IF(cond2,result1,result2),result3)

Try SWITCH(TRUE(),
cond1,res1,

cond2,res2,
cond3,res3)

DAX:

Test = SWITCH(TRUE(),

SUM('Table (8)'[Frequence])=SUM('Table (8)'[Scan_frequency]),"OK",

SUM('Table (8)'[Frequence])>SUM('Table (8)'[Scan_frequency]),"KO",

SUM('Table (8)'[Frequence])<SUM('Table (8)'[Scan_frequency]),"N_OK")



End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[cgd210]

Thank you so much for the idea. It's good, but something else is still wrong in my case, because I tried to do what you proposed and it shows all the results as N_OK. In addition, you have considered that the information was all in the same table (Table 8), but I have tested your proposal using the data distributed in Table A and Table B, because in the database that will feed the dashboard will be separated. Another thing is that I can't use the Test calculation result as input for a slicer. Do you know why this problem may be happening? Thank you very much! 🙂

[ValtteriN · Accepted solution]

Hi,

I included all the data in the same table in my example, since from a data modelling perspective it would be good if you added all the relevant details into a single table using powerquery. Other option is to use functions like RELATED to get the information to a single table. 

For slicer options  you can create a slicer table and use that in combination with a measure. E.g.

SlicerMeasure_ = IF(ISFILTERED('Table (9)'),

IF([Test]=MAX('Table (9)'[Column1]),1,0),1

)

[cgd210]

Thank you so much for your help. It worked!