Count # of Fields used <=5 times

[Nazeer_at_ADS]

Hi There,

I'm very new to Power-BI and I need some help with DAX formula.

Scnario:

I have two columns with Field Name and Session ID. I want the distinct Fields count which are used less then equal to 5 times (using Session ID). I was able to create below table where we can see the Field Names and Distinct Count of Session.

Data Sample:

Field Name	Count of Session ID (Distinct)
Fiscal Year	1
Fiscal Quarter	1
Service Indicator	2
Product	3
Country	5
Architectures	5
Geo	7
Segments	9
Total Billed	11
Revenue Flag	15

Expected Result:

Fields Used <= 5 Times: 6

Please can you help me with it.

Many Thanks

Nazeer

[harshnathani]

Hi @Nazeer_at_ADS ,

Try this measure

Fields less than 5eq times = 
var dc = CALCULATE(DISTINCTCOUNT('Table'[Field Name]),'Table'[Count of Session ID (Distinct)] <=5)
RETURN
dc

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@Nazeer_at_ADS , Try like

countx(values(Table[Field Name]),if([Count of Session ID (Distinct)]<5,Table[Field Name],blank()))

[mahoneypat]

Please try this measure expression

Fields Used <6 times =
COUNTROWS (
    FILTER (
        VALUES ( Table[Field Name] ),
        CALCULATE ( DISTINCTCOUNT ( Table[SessionID] ) ) <= 5
    )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[lbendlin]

use countrows() with a filtered table.

something like 

Countrows(filter(table,table[Count of Session ID (Distinct)]<6))

[Nazeer_at_ADS]

Thanks Ibendlin for your reply. 

I'm not getting the desired output. I want the count of Field Name's which are used less than 6 times. 

If you look at the below, it says Fiscal Year and Fiscal Quarter were used only once (based on Session ID Count). Service Indicator was used twice and Product 3 times. Now based on the Session ID count for each Field Name I want to count distinct Field Names which were used less <=5 times or <6 times.  

The result from below table should be: 4 Fields

Sample Data:

Field Name	Session ID
Fiscal Year	101
Fiscal Quarter	101
Service Indicator	101
Service Indicator	102
Product	101
Product	102
Product	103

-Nazeer