Forum Discussion

pmcmonag's avatar
pmcmonag
Advocate IV
5 years ago

DAX Virtual tables & total row evaluation

Is it possible to create a virtual table which returns a different measure for each row based on some logic……….and then expect SUMX to produced the desired result in the total row?

 

Attached is a dummy .pbix of the scenario required in full (mimicking coding and source systems from real data). Presenting it as a DAX problem, but could well be a data modelling answer!

Dummy pbix 

 

Summary:

Require a solution to view Income in the view of a Corporate partner to a Charity, where applicable, if not just view it as an individual supporter. Scenario is that Microsoft could give a donation from Head Office, their UK office or an employee could run a race for them. So the standard view would be income per those three Supporter records. However also want to see the view of Microsoft as a whole entity.

 

This seems to be OK, but then add another individual who is not coded to raised for Microsoft and the current measure doesn’t evaluate correctly.

 

 

 

 

DAX
evaluation context
Totals
Virtual Table

1 Reply

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi pmcmonag 

    You need to trigger context transition for it to work propertly (so that the COUNTROWS takes into account what you have in the row of the visual)  . Just add a CALCULATE:

    Total Income (Dynamic) V2 =
VAR TempTable =
    ADDCOLUMNS (
        VALUES ( Supporters[SupporterID] ),
        "Income",
            IF (
                CALCULATE ( COUNTROWS ( 'Corporate Finance Codes' ) ) > 0,
                [Total Income (by Corporate)],
                [Total Income]
            )
    )
RETURN
    SUMX ( TempTable, [Income] )

     

    OR alternatively, a version a bit more compact:

    Total Income (Dynamic) V3 =
SUMX (
    DISTINCT ( Supporters[SupporterID] ),
    IF (
        CALCULATE ( COUNTROWS ( 'Corporate Finance Codes' ) ) > 0,
        [Total Income (by Corporate)],
        [Total Income]
    )
)

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.