Count in function of other filters

[Tjordaske85]

At the left you'll find a simple table, at the right you'll find the tables I want to create in Power BI:

First a small explanation of the left table:

- You'll see that project 'Building A' is started in august when the first List is filled in.

- Every time a list of a certain ListTempNr is filled in, the ListNr goes 1 up. The ListNr for every project starts from 0, even when they are using the same ListTempNr.

At the right you'll find in column G and H some ordinary filter options I'm going to create in Power BI.

So one to select the Month and one to select the ListTempNr

First Question: how to create with a DAX-formula column '#ListNr'. This value is the count of unique ListNr according the selected Month and ListTempNr?

In the table at the bottom right there is a KPI. This is "OK" if the "#ListNr" for the current Month and ListTempNr = A is greater than 0, else it is "NOK". Second Question: How to create this with a DAX-formula?

My current knowledge of DAX is 0. I'm going to follow a course but in the meantime I want to start creating some basic dashboards. Thanks in advance!!

[v-yangliu-msft]

Hi  @Tjordaske85 ,

Creating a calculation table acts as the main function of Slicer, which has no direct relationship with the data in the main table, and will affect the display of data in the main table through the creation of Measure.

If you directly use Month and ListTempNr of the main table as Slicer, such as Month=October, ListTempNr=B, in this case there is no Building C, it will not be displayed directly.

If you are the current month and a fixed list, you can modify to the following function:

KPI =
var _value=
SUMMARIZE('Table','Table'[Projectname],"if",
CALCULATE(DISTINCTCOUNT('Table'[ListNr]),FILTER(ALL('Table'),
'Table'[Projectname]=MAX('Table'[Projectname])&&'Table'[Month] = MONTH(TODAY()) &&'Table'[ListTempNr]="A")))
var _if=
FILTER(_value,[if]>=1)
return
IF(
MAX('Table'[Projectname]) in SELECTCOLUMNS(_if,"if1",[Projectname]),"OK","NOK")

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tjordaske85]

Hi @v-yangliu-msft,

Thank you for helping me out!!

1. Do I need to create those calculated tables? I'm going to use the basic filters to select the Month and ListTempNr, so can't the '#ListNr' not calculate his value based on those filters?

2. The DAX-formula for the KPI also take into account the variables '_selectMonth' and '_selectTermNr'. This needs to be always the current Month and a fixed ListTempNr. How does this change the DAX-formula?

Again! Thanks you already very very much!!!

[v-yangliu-msft · Accepted solution]

Hi  @Tjordaske85 ,

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create calculated column.

Flag =
var _min=
MINX(FILTER(ALL('Table'),'Table'[Projectname]=EARLIER('Table'[Projectname])&&'Table'[Creation date]=EARLIER('Table'[Creation date])),[Index])
var _if=
IF(
    'Table'[Index]=_min,1,0)
return
_if

ListNr =
SUMX(FILTER(ALL('Table'),'Table'[Projectname]=EARLIER('Table'[Projectname])&&'Table'[ListTempNr]=EARLIER('Table'[ListTempNr])
&&'Table'[Index]<=EARLIER('Table'[Index])),[Flag])

QuestionNr =
RANKX(FILTER(ALL('Table'),'Table'[Projectname]=EARLIER('Table'[Projectname])&&'Table'[ListTempNr]=EARLIER('Table'[ListTempNr])
&&'Table'[Creation date]=EARLIER('Table'[Creation date]))
,[Index],,ASC)

Month =
MONTH('Table'[Creation date])

3. Create calculated table.

TempNr =
DISTINCT('Table'[ListTempNr])

Month_Slicer =
DISTINCT('Table'[Month])

4. Create measure.

# ListNr =
var _selectMonth=SELECTEDVALUE('Month_Slicer'[Month])
var _selectTermNr=SELECTEDVALUE('TempNr'[ListTempNr])
var _value=
CALCULATE(DISTINCTCOUNT('Table'[ListNr]),FILTER(ALL('Table'),
'Table'[Projectname]=MAX('Table'[Projectname])&&'Table'[Month] = _selectMonth&&'Table'[ListTempNr]=_selectTermNr))
return
IF(
    _value=BLANK(),0,_value)

Okay =
IF(
    [# ListNr] =0,"No","Yes")

KPI =
var _selectMonth=SELECTEDVALUE('Month_Slicer'[Month])
var _selectTermNr=SELECTEDVALUE('TempNr'[ListTempNr])
var _value=
SUMMARIZE('Table','Table'[Projectname],"if",
CALCULATE(DISTINCTCOUNT('Table'[ListNr]),FILTER(ALL('Table'),
'Table'[Projectname]=MAX('Table'[Projectname])&&'Table'[Month] = _selectMonth&&'Table'[ListTempNr]=_selectTermNr)))
var _if=
FILTER(_value,[if]>=1)
return
IF(
MAX('Table'[Projectname]) in SELECTCOLUMNS(_if,"if1",[Projectname]),"OK","NOK")

5. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly