Finding number of re-used values based on slicers

[Deemo]

Hi All,

I need to find total number of flows re-used for more than one department based on date and department slicer.

Department	Flow	Date
Dept 1	Flow1	May
Dept 1	Flow2	June
Dept 1	Flow3	July
Dept 1	Flow4	Aug
Dept 2	Flow3	June
Dept 2	Flow5	June
Dept 3	Flow1	May
Dept 3	Flow3	July
Dept 3	Flow4	Aug

If I select All departments and date May - June, it shoud display 1 ,as there is only one flow(Flow1) being used in more than 1 department(Dept1,Dept3)

If I select All departments and date July - Aug, it should display 2, as there are two flows being used in more than 1 dept.

If I select Dept1 and date May - June, it shoud display 1 ,as there is one flow(Flow1) being shared by more than 1 department(Dept1,Dept3)

Please help.

Thanks in advance!

[Mariusz]

Hi @Deemo 

Please see the attached file with a solution, for it to work you will need to add a Department Dimension

Measure = 
VAR __flow = VALUES( 'Table'[Flow] )
RETURN 
CALCULATE(
    SUMX(
        __flow, 
        CALCULATE( INT( COUNTROWS( 'Table' ) > 1 ) )
    ),
    ALLSELECTED( 'Table' ),
    ALL( Department ),
    __flow
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mariusz]

Hi @Deemo 

Try something like this 

Measure = 
VAR __flow = VALUES( 'Table'[Flow] )
RETURN 
CALCULATE(
    SUMX(
        __flow, 
        CALCULATE( INT( COUNTROWS( 'Table' ) > 1 ) )
    ),
    ALLSELECTED(),
    __flow
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Deemo]

Thanks for your reply Mariusz.

I tried the measure you suggested, It is also considering flows which are being repeated within same department for different dates. However, I want to count only those distinct flows which are being used in more than one departments.

[Mariusz]

Hi @Deemo 

Actually adding ALL( 'Table'[Department] ) to my original measure should work as well

Measure = 
VAR __flow = VALUES( 'Table'[Flow] )
RETURN 
CALCULATE(
    SUMX(
        __flow, 
        CALCULATE( INT( COUNTROWS( 'Table' ) > 1 ) )
    ),
    ALLSELECTED( 'Table' ),
    ALL( 'Table'[Department] ),
    __flow
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Deemo]

Hi Mariusz,

I modified highligted in your pbix . I didn't get why total and measure is showing 1 in your pbix, whereas in my case, it shows sum of measure which is 3. I am getting same result from nandukrishnavs's  DAX.

Also in my model, if flow3 with dept 1 has another record in May with diffrent date or time, the measure is showing 1.

Date, Department and Flows are dimensions, and MainTable has PKs and date with timestamp.

Can you please help me here?

[nandukrishnavs]

@Deemo  - try this 

FlowCount =
VAR _gp =
    CALCULATETABLE (
        SUMMARIZE ( FlowTable, FlowTable[Flow], "CountVal", COUNT ( FlowTable[Flow] ) ),
        ALL ( FlowTable[Department] )
    )
VAR _flowcount =
    CALCULATE ( COUNTROWS ( FILTER ( _gp, [CountVal] > 1 ) ) )
RETURN
    _flowcount

If you find this solution useful, please mark this post as an accepted solution.

Regards,

Nandu Krishna

[Deemo]

Thanks for your reply nandukrishnavs.

The measure is not displaying anything. I aslo see that you are counting number of flow directly, however I want only those distinct flows which are being used in multiple departments. 

[nandukrishnavs]

@Deemo  - Is it possible to share your pbix file?

[nandukrishnavs]

@Deemo  - It is working for me.