Count from multiple columns

[avicric]

Hi All,

I need help to create a measure which would provide the count of spareparts used
I have two sheets Sparepart dataset and unique sparepart list
My data set
Case id,
Status,
Sparepart1
Sparepart2
Sparepart3
Sparepart4
Sparepart5
Sparepart6
Sparepart7
Sparepart8
Depending on the repair case multiple spareparts can be used
So if only 1 spartpart was used entry will be only in sparepart1 and if 2 spareparts were used entry will be in Sparepart1 & Sparepart2
I have tried to create a measure
Spares:=VAR P1=Calculate(Countrows(dataset sheet),Sparepart1<>Blank())
P2=Calculate(Countrows(dataset sheet),Sparepart2<>Blank())
P3=Calculate(Countrows(dataset sheet),Sparepart3<>Blank())
P4=Calculate(Countrows(dataset sheet),Sparepart4<>Blank())
P5=Calculate(Countrows(dataset sheet),Sparepart5<>Blank())
P6=Calculate(Countrows(dataset sheet),Sparepart6<>Blank())
P7=Calculate(Countrows(dataset sheet),Sparepart7<>Blank())
P8=Calculate(Countrows(dataset sheet),Sparepart8<>Blank())
RETURN
P1+P2+P3+P4+P5+P6+P7+P8

This is is duplicating the counts

 

[jorismoerings]

Hi,

I don't understand what you're trying to do.
The attempted measure can never be right because it's missing to much elements however when corrected for the mistakes in DAX it should read like this:
Spares:=
VAR P1=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart1]<>Blank()))
VAR P2=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart2]<>Blank()))
VAR P3=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart3]<>Blank()))
VAR P4=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart4]<>Blank()))
VAR P5=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart5]<>Blank()))
VAR P6=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart6]<>Blank()))
VAR P7=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart7]<>Blank()))
VAR P8=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart8]<>Blank()))
RETURN
P1+P2+P3+P4+P5+P6+P7+P8

This will not duplicatre the count of spareparts used. It will however duplicate the count of the number of cases in which a sparepart is used.
Hopes this helps.

 

[avicric]

Hi,

I don't understand what you're trying to do.
The attempted measure can never be right because it's missing to much elements however when corrected for the mistakes in DAX it should read like this:
Spares:=
VAR P1=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart1]<>Blank()))
VAR P2=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart2]<>Blank()))
VAR P3=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart3]<>Blank()))
VAR P4=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart4]<>Blank()))
VAR P5=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart5]<>Blank()))
VAR P6=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart6]<>Blank()))
VAR P7=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart7]<>Blank()))
VAR P8=Calculate(Countrows(dataset sheet),FILTER(dataset sheet;[Sparepart8]<>Blank()))
RETURN
P1+P2+P3+P4+P5+P6+P7+P8

This will not duplicatre the count of spareparts used. It will however duplicate the count of the number of cases in which a sparepart is used.
Hopes this helps.

You are correct....
I have a unique sparepart list against which i want to show the counts...
With filters/slicers for Month/Region/Product etc....