Forum Discussion

TriveniN's avatar
TriveniN
Icon for Helper II rankHelper II
2 years ago
Solved

how to find same Position id is associate to multiple Resurce ids using DAX

Hi ,

 

Can anyone help on below query?

Data given below 

Position IDResource_ID
P_00001R_0001
P_00001R_0002
P_0123R_01
P_0123R_02
P_456R_90
P_456R_80
P_567R_4
P_876R_5
P_36R_6

 

I need to get same Position id is associate to multiple Resurce ids using DAX.

 

Expected output

Position IDResource_ID
P_00001R_0001
P_00001R_0002
P_0123R_01
P_0123R_02
P_456R_90
P_456R_80

 

Regards,

Triveni N.

Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    Hi TriveniN 

     

    How would you like to achieve this?

    In the query editor to remove the data before being brough into the report or as a table visual in the Report view?


    As a table visual apply a filter where the DistinctCount > 1

    Create a measure that checks the distinct count and then use this in the filter pane.

    Distinct ResourceID =
    CALCULATE(DISTINCTCOUNT('Table'[Resource_ID]), all('Table'[Resource_ID]))
     
    Or in the query editor merge the table to itself

    Then change the aggregation to Count ResourceID

    Use the dropdown to filter to not 1

     

    Now those rows wont be imported into the model at all.

  • divyed's avatar
    divyed
    2 years ago

    Hello TriveniN ,

     

    Do you want a measure instead ? Your expected output from screenshot is a table with two columns.  

     

    You can modify query to get position_id only :

    Selected =
        SUMMARIZE (
            FILTER (
                'Sample',
                CALCULATE ( COUNT ( 'Sample'[Resource_ID] ), ALLEXCEPT ( 'Sample', 'Sample'[Position_ID] ) ) > 1
            ),
            'Sample'[Position_ID]
        )
     
    I f you want a measure to show number of position_ids having more than 1 resource_Ids, use below query:
     
    Selected_POs_Measure =
        CALCULATE (
            DISTINCTCOUNT ( 'Sample'[Position_ID] ),
            FILTER (
                'Sample',
                CALCULATE ( COUNT ( 'Sample'[Resource_ID] ), ALLEXCEPT ( 'Sample', 'Sample'[Position_ID] ) ) > 1
            )
        )
     
    I hope this helps
    Cheers

6 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Icon for Super User rankSuper User
    2 years ago

    Hi TriveniN 

     

    How would you like to achieve this?

    In the query editor to remove the data before being brough into the report or as a table visual in the Report view?


    As a table visual apply a filter where the DistinctCount > 1

    Create a measure that checks the distinct count and then use this in the filter pane.

    Distinct ResourceID =
    CALCULATE(DISTINCTCOUNT('Table'[Resource_ID]), all('Table'[Resource_ID]))
     
    Or in the query editor merge the table to itself

    Then change the aggregation to Count ResourceID

    Use the dropdown to filter to not 1

     

    Now those rows wont be imported into the model at all.

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    2 years ago

    TriveniN 

    Create a new table:

    FilteredTable1 = 
    VAR PositionCounts =
    SUMMARIZE(
    'Table', 
    'Table'[Position ID],
    "Count", COUNTROWS('Table')
    )
    RETURN
    FILTER(
    'Table',
    'Table'[Position ID] IN 
    SELECTCOLUMNS(
    FILTER(PositionCounts, [Count] > 1),
    "Position ID", 'Table'[Position ID]
    )
    )

     

    If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

    • TriveniN's avatar
      TriveniN
      Icon for Helper II rankHelper II
      2 years ago

      Getting all the records irrespective of position id

  • divyed's avatar
    divyed
    Icon for Super User rankSuper User
    2 years ago

    Hello TriveniN ,

     

    Please use below DAX to filter your table :

     

    Selected_Positions =
        FILTER (
            'Sample',
            CALCULATE ( COUNT ( 'Sample'[Resource_ID] ), ALLEXCEPT ( 'Sample', 'Sample'[Position_ID] ) ) > 1
        )
     

     

    I hope this is what you are looking for. Please mark this as a solution if this has fixed your issue. Want to say special thank you , press a kudos button 🙂

     

    Cheers

    • TriveniN's avatar
      TriveniN
      Icon for Helper II rankHelper II
      2 years ago

      The expression refers to multiple columns. Multiple columns can't be converted to scalar value 

      • divyed's avatar
        divyed
        Icon for Super User rankSuper User
        2 years ago

        Hello TriveniN ,

         

        Do you want a measure instead ? Your expected output from screenshot is a table with two columns.  

         

        You can modify query to get position_id only :

        Selected =
            SUMMARIZE (
                FILTER (
                    'Sample',
                    CALCULATE ( COUNT ( 'Sample'[Resource_ID] ), ALLEXCEPT ( 'Sample', 'Sample'[Position_ID] ) ) > 1
                ),
                'Sample'[Position_ID]
            )
         
        I f you want a measure to show number of position_ids having more than 1 resource_Ids, use below query:
         
        Selected_POs_Measure =
            CALCULATE (
                DISTINCTCOUNT ( 'Sample'[Position_ID] ),
                FILTER (
                    'Sample',
                    CALCULATE ( COUNT ( 'Sample'[Resource_ID] ), ALLEXCEPT ( 'Sample', 'Sample'[Position_ID] ) ) > 1
                )
            )
         
        I hope this helps
        Cheers