Use filter and REMOVEFILTERS for same measure

[apoorvsi]

Hi, 

I am fairly new to PBI and have a requirement that I am unable to convert to DAX. 

I have a measure called ReUseCount which counts for the occurence of a component in different companies. This Company is also used as a slicer in the same page.

I have used the following DAX:

calculate ( DISTINCTCOUNT('Component Name'[Component Name]),

,Filter('Component Name',

CALCULATE (

DISTINCTCOUNT ('Fact'[Company])

,REMOVEFILTERS ( Company[Company Name] )

)>1 )

Example: 

CompanyA     Component1

CompanyB     Component1

CompanyB     Component2

CompanyC     Component2

CompanyC      Component3

Expected result: When all companies are selected, I get output 2 (Components 1,2 are reused i.e. present in more than 1 company) which is correct. However, when the Company is filtered, say CompanyA, I still get output 2 whereas it should be 1 (Only Component1 of CompanyA is reused)

Please help. TIA.

[parry2k · Accepted solution]

@apoorvsi try this measure

Measure 3 = 
CALCULATE (
    DISTINCTCOUNT ('Table (2)'[Component] ), 
    FILTER ( 
        SUMMARIZE ( 
            ALL ('Table (2)' ),
            'Table (2)'[Component],
            "cnt", DISTINCTCOUNT ( 'Table (2)'[Company] ) 
        ),
        [cnt] > 1
    )
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[apoorvsi]

Hi @parry2k , coming to you for a related doubt.

How may I convert:

FILTER ( 
        SUMMARIZE ( 
            ALL ('Table (2)' ),
            'Table (2)'[Component],
            "cnt", DISTINCTCOUNT ( 'Table (2)'[Company] ) 
        ),
        [cnt] > 1

 to a table? I need to use this in intersect function.

Basically, need all Components with distinct count of company >1 and compare their presence in :

FILTER ( 
        SUMMARIZE ( 
            'Table (2)',  -- filtered
            'Table (2)'[Component],
            "cnt", DISTINCTCOUNT ( 'Table (2)'[Company] ) 
        ),
        [cnt] > 1

[amitchandak]

Try a measure like

countx(filter(summarize(table, table[company],table[Component], "_1", calculate(count(table[company]),filter(all(Table), table[Component] =max(table[Component])))),
[company] in values(table[company])),[Component])

[apoorvsi]

Hi @amitchandak , thanks for replying, I am not sure I understand the use of table[Component] =max(table[Component]))
Could you please elaborate?

[parry2k]

@apoorvsi did you tried the solution I provided.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[apoorvsi]

Hi @parry2k , thanks, I am working with the measure you provided but it is not returning expected values. I think I should have mentioned that the source is not the same table. The companies to be counted are from fact and the components are from dimension.

 CALCULATE (

DISTINCTCOUNT ('Component Name'[Component Name] ),

FILTER (

SUMMARIZE (

ALL ('Component Name' ),

'Component Name'[Component Name],

"cnt", CALCULATE (

DISTINCTCOUNT ('Fact'[CompanyPk])

--,FILTER(ALL('Execution Date'),'Execution Date'[Execution Date] >= StartDate && 'Execution Date'[Execution Date] <= EndDate )

)),[cnt]>1

))

Do you see any issues?

[parry2k]

@apoorvsi this is totally different but logically it should work, how these tables are related, can you share real-time model scenario with sample data.

[apoorvsi]

Hi @parry2k ,

Sharing the sample data:

Fact Table

ComponentNamePk	CompanyPk	EntityId
1002	37	AP_001
1002	46	AP_001
1003	46	AP_002
1003	37	AP_002
1891	69	AP_002
4251	9	AP_001
698	27	AP_001
698	37	AP_001
698	46	AP_001
698	46	AP_004

Dim Company Table:

CompanyPk	CompanyName
37	abc
46	xyz
69	tef
9	ghg
27	fdjkf

Dim Component Table:

ComponentNamePk	ComponentName	EntityId
1002	componentname1	AP_001
1891	blocrt	AP_001
4251	oker	AP_001
698	monitoring	AP_001
5258	componentname1	AP_002
655	blocrt	AP_005
658	oker	AP_003
586	monitoring	AP_002

The granularity of Dim Component is not based only on name, rather the name and an entity ID. I only want to consider the entity ID AP_001 which I am filtering out in my visual.

[apoorvsi]

Hi @parry2k , I tried using all fields from fact itself and it sems to work now.. thank you very much!! I'll check some more.  

[parry2k]

@apoorvsi good to know. cheers!!