Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Crossfilter to return text values

Hi,

Following is a brief/limited snapshot of my model:

 

I have two tables Entity Master and Entity Consol Master, joined 1-M with single side filter. The Entity Consol Status has a Status flag - Consol Status(Y/N). I need the output that whenever I use the Status flag as a filter, it filters out the slicers from Entity Master. I know that this can be achieved if I convert the filter direction between Entity Master and Entity Consol Status to 'Both'. But this is not possible due to my model limitations (other joins). Is there any way I can achieve it through DAX?

 

I think I need to use CrossFilter. But it will work only with functions like Calculate. And Calculate does not work on text columns.

crossfilter

8 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Hi, Anonymous , to my understanding, the issue results from a requirement of "Uphill filtering"(from Many to One) in contrast to intrinsic filter propagation from One to Many. Pls refer to this blog dedicated to addressing such a common problem.

    This blog offered a detailed solution, especially the Expanded Table one in the last part, a brilliant black magic by Italians.😆

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi CNENFRNL 

      The Blog talks about using a Calculate function, but in my case I am unable to use it as I need to return a text value. Any help, how I can use Calculate for my use case?

       

      Thanks,

      Shailee.

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Anonymous Yes, you can surely use CALCULATE like this,

     

    Calculate Text 1 =
VAR __status =
    MAX ( 'Entity Consol Master'[Consol Status] )
RETURN
    CALCULATE (
        CONCATENATEX (
            VALUES ( 'Entity Master'[EntityName] ),
            'Entity Master'[EntityName],
            UNICHAR ( 10 )
        ),
        'Entity Consol Master'[Consol Status] <> __status,
        CROSSFILTER ( 'Entity Consol Master'[Entity Code], 'Entity Master'[Entity_Code], BOTH )
    )

     

    or

     

    Calculate Text 2 =
VAR __status =
    MAX ( 'Entity Consol Master'[Consol Status] )
RETURN
    CALCULATE (
        CONCATENATEX (
            VALUES ( 'Entity Master'[EntityName] ),
            'Entity Master'[EntityName],
            UNICHAR ( 10 )
        ),
        FILTER (
            ALL ( 'Entity Consol Master' ),
            'Entity Consol Master'[Consol Status] <> __status
        )
    )

     

     

     

    Better to attach a mockup file for further debugging if aforementioned measures don't work properly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      CNENFRNL 

       

      I do not want to create the calculated column on Entity Master, as there are many other columns in this master which can be used for slicing on the report.

      I am sorry, but I am unable to share any files due to access restrictions.

      Another approach to my requirement can be:

      If I update the model by reversing the filter directions as below - Change Filter direction between Entity Master and Entity Consol Status to Both and change filter direction between Entity Consol Status and Date to Single (as both the filter direction to 'Both' is creating circular reference.

      Then the issue with slicing on Entity Master is resolved. But it results in cartesian product with date as below :

      The first table depicts the actual value of the Consol status flag for Entity 886, for given periods/quarters. Second table shows the Cumulative Amount for all selected periods. But as soon as I pull Cumulative amount, a cartesian product is created for periods where the staus is 'N'. This works fine when the filter between Entity Consol Status and Date is bi-directional. Can I create a calculated column on Entity Consol Status, based on the Consol Status flag but overriding the filter with date to Both side filter such that the Cartesian issue is resolved?

       

      Thanks much!

      Shailee.

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        5 years ago

        Hi, Anonymous , in my opinion, you've overcomplicated such an issue. In fact, bi-direction relationship is NOT INDISPENSABLE in solving such "M to 1 filtering" issues; furthermore, "1 to M" is the only recommendation by DAX gurus and experts to normal users like us.

         

        What I wrote are measures instead of calculated column formulae; the measures display all distinct 'Entity Master'[EntityName] items after filtering out what is sliced in column 'Entity Consol Status'[Consol Status], e.g. supposing 'Entity Consol Status'[Consol Status] = "Y", filter of 'Entity Consol Status'[Consol Status] = "N" can be propagated to 'Entity Master' and the measures displays filtered 'Entity Master'[EntityName] items.

         

        By the same means, a relationship between Date(1) - 'Entity Consol Status'(M) is enough to implement such inverse filter propagation (from 'Entity Consol Status'(M) to Date(1)).