Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
pmcmonag
Advocate IV
Advocate IV

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.

 

pmcmonag_1-1621876187458.png

 

 

 

1 REPLY 1
AlB
Super User
Super User

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

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors