Forum Discussion

rajeshapunu1234's avatar
rajeshapunu1234
Icon for Helper I rankHelper I
2 years ago
Solved

Finding the common item from the multiples category

Dear team,   I am having a situation like i am having Table name called category and which table is having category and item column the category column and many caterogy and each category have dis...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    expected result measure only item: = 
VAR _categorycount =
    COUNTROWS ( ALLSELECTED ( Data[Category] ) )
VAR _currentcategory =
    VALUES( Data[Category] )
VAR _currentitem =
    SELECTEDVALUE ( Data[Item] )
VAR _t =
    ADDCOLUMNS (
        ALLSELECTED ( Data ),
        "@count", COUNTROWS ( FILTER ( ALLSELECTED ( Data ), Data[Item] = _currentitem ) )
    )
RETURN
    SUMX (
        FILTER (
            _t,
            Data[Category] in _currentcategory
                && Data[Item] = _currentitem
                && [@count] = _categorycount
        ),
        [@count]
    )
rajeshapunu1234's avatar
rajeshapunu1234
Icon for Helper I rankHelper I
2 years ago

This my table below is the screen where i followed your instruction

but i am not getting proper result
can you please guid me here

 

  • rajeshapunu1234's avatar
    rajeshapunu1234
    Icon for Helper I rankHelper I
    2 years ago

    Hi Jiwan Kim
    you understood my point very clearly and it is working now

    Thanks for your support

    adding to that is it possible to get the item name seperately whithc distinct without package name

    Only common item name (distinct) should show whenever selectes the packes in the slicer

    thanks

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

      Hi,

      Please check the below picture and the attached pbix file.

       

       

       

      expected result measure only item: = 
VAR _categorycount =
    COUNTROWS ( ALLSELECTED ( Data[Category] ) )
VAR _currentcategory =
    VALUES( Data[Category] )
VAR _currentitem =
    SELECTEDVALUE ( Data[Item] )
VAR _t =
    ADDCOLUMNS (
        ALLSELECTED ( Data ),
        "@count", COUNTROWS ( FILTER ( ALLSELECTED ( Data ), Data[Item] = _currentitem ) )
    )
RETURN
    SUMX (
        FILTER (
            _t,
            Data[Category] in _currentcategory
                && Data[Item] = _currentitem
                && [@count] = _categorycount
        ),
        [@count]
    )
      • rajeshapunu1234's avatar
        rajeshapunu1234
        Icon for Helper I rankHelper I
        2 years ago

        Sir thank you so much your DAX function working as per my expectation
        so now i can get the common item from the selected category by clicing the filter "is not blank"
        and i can get the other then the common items like the item can be available in both but not not matching

        is it possible to take the item which is not available in the both categery by using this dax

        if possible please let me know how can acheive it

        thanks