Facing issue with Filtering Data and Measure

[NavaneethaRaju]

Hi, 

I have enclosed sample data from my model.
My requirment:

I have a Tag ID, Tag_Owner, Order_Owner, Procedures these four fields.

Task 1. Tag ID Number - 1065,  Order Owner is done the more procedures, So i want only to show that Tag ID Used and Shared Procedures in My table.
Task 2. I want to restrict the Tag Id Where the Order Owner procedure is less than a Tag Owner(Tag ID 1221). 

I enclosed the Total Procedures and %of Usage Measure. 

Total Procedures =

SUMX (

    VALUES ( 'Tag Usage Fact'[Tag ID] ),

    CALCULATE (

        SUM ( 'Procedure Fact'[Procedures] ),

        ALLEXCEPT (

            'Tag Usage Fact',

            'Tag Usage Fact'[Tag ID]

        ),

        FILTERS ( 'Tag Usage Fact'[Order Date] ),FILTERS('Tag Usage Fact'[Tag Owner Name])

    )

)

% of Usage = SUM('Procedure Fact'[Procedures])/[Total Procedures]

Tag IDSet IDSet NameSystem NameTag Owner NameOrder Owner NameProceduresTotal Procedures% of UsageUsage Type

1065	926.9	Posterior Auxiliary Instruments Set	Posterior Disc Prep Instruments	AT MED	AT MED	1	3	33%	Used
1065	926.9	Posterior Auxiliary Instruments Set	Posterior Disc Prep Instruments	AT MED	PP/AD	2	3	67%	Shared
1221	924.902	REVERE Instrument I	REVERE 5.5 Degen Ti System	Jean P	Jean P	44	46	96%	Used
1221	924.902	REVERE Instrument I	REVERE 5.5 Degen Ti System	Jean P	Travis	2	46	4%	Shared

Please advice how to show details in table as per Task 1.

[Anonymous · Accepted solution]

Hi @NavaneethaRaju ,

You can follow the below steps to get it:

1. Create a measure as below to judge if the current Tag ID fulfill the conditions

Flag = 
VAR _seltagid =
    SELECTEDVALUE ( 'Tag Usage Fact'[Tag ID] )
VAR _pusageorder =
    SUMX (
        FILTER (
            'Tag Usage Fact',
            'Tag Usage Fact'[Tag ID] = _seltagid
                && 'Tag Usage Fact'[Tag Owner Name] = 'Tag Usage Fact'[Order Owner Name]
                && 'Tag Usage Fact'[Usage Type] IN { "Used", "Shared" }
        ),
        [% of Usage]
    )
VAR _pusagetag =
    SUMX (
        FILTER (
            'Tag Usage Fact',
            'Tag Usage Fact'[Tag ID] = _seltagid
                && 'Tag Usage Fact'[Tag Owner Name] <> 'Tag Usage Fact'[Order Owner Name]
                && 'Tag Usage Fact'[Usage Type] IN {  "Used", "Shared" }
        ),
        [% of Usage]
    )
RETURN
    IF ( _pusageorder <> _pusagetag, 1, 0 )

2. Create a visual and apply a visual-level filter on the visual with the condition (Flag is 1)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards