Crossfilter to return text values

[Anonymous]

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.

[CNENFRNL]

@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]

@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]

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)).

[Anonymous]

@CNENFRNL 

The user requirement is such that they want to use Entity Name (or any other columns from Entity Master) and Consol Status as report level filters. The logic of Consol Status is that Few entities from Entity Master (not all, say 100 out of 500) get consolidated for a specific period, e.g. a particular Entity 'A' is getting consolidated for Q1 & Q2 for FY20 and not consolidated for Q3 & Q4. Accordingly Consol Status is 'Y' for Entity 'A' for Q1&Q2 and N for Q3 & Q4. But the user also wants that when I use the Consol Status filter/slicer at report as ='Y', the Entity Name filter/slicer only shows the specific Entities which are getting consolidated i.e. only the list of 100. Hence this model. I cannot create measures, as these need to be used as filters.

Any idea, why the following DAX code for a calculated column, is not overriding the filter b/n Date and Entity Consol Date from 'Single' in model to 'Both' direction? Is there any other way to achieve this?

Consol Status New =

Var X = 'Entity Consol Status'[Consol Status ]

Var Y = CALCULATE(X,CROSSFILTER('Date'[Date],'Entity Consol Status'[Date],Both))

return Y

[CNENFRNL]

@Anonymous If you attach a mockup pbix file, it's way much easier to show your issues and highly likely a solution arrives sooner.

[Anonymous]

@CNENFRNL , I am not able to share files outside my organization

[CNENFRNL]

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]

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.