Get Dynamic Table Based on Slicer Value

[Kyle_Willey]

Hello all,

I have done some research already and I know why my initial idea failed. Here is my dilemma:

I have several tables with the same columns. Each one has a different set of users, hospitals in this case. For each unit in the hospital, the database enters information on one row per unit per month. i.e.:

SummaryYM	location	infCount	numCLdays	etc...
1/1/2023	Unit1	1	100	...
1/1/2023	Unit2	0	200	...
2/1/2023	Unit1	0	150	...

What I am trying to do is create a dynamic table that pulls the above data from the table that matches the slicers used. I can RETURN each variable and get the result I need. However, I get a scalar error any time I try to use an IF statement or a SWITCH statement to have it dynamically change based on the slicers.

What I really am looking for is to be able to give a timeline of active events with associated units from one of the 4 tables, with the table selected based on the slicer chosen. Am I overthinking a different way to do this?

DAX so far:

CLABSIDynamicTable =
VAR _ACH =
    FILTER (
        SUMMARIZE (
            'CLABSI ACH SIR',
            'CLABSI ACH SIR'[summaryYM],
            'CLABSI ACH SIR'[location],
            'CLABSI ACH SIR'[infCount],
            'CLABSI ACH SIR'[numcldays]
        ),
        'CLABSI ACH SIR'[location] <> ""
    )
VAR _IRF =
    FILTER (
        SUMMARIZE (
            'CLABSI IRF SIR',
            'CLABSI IRF SIR'[summaryYM],
            'CLABSI IRF SIR'[location],
            'CLABSI IRF SIR'[infCount],
            'CLABSI IRF SIR'[numcldays]
        ),
        'CLABSI IRF SIR'[location] <> ""
    )
VAR _IQR =
    FILTER (
        SUMMARIZE (
            'CLABSI IQR SIR',
            'CLABSI IQR SIR'[summaryYM],
            'CLABSI IQR SIR'[location],
            'CLABSI IQR SIR'[infCount],
            'CLABSI IQR SIR'[numcldays]
        ),
        'CLABSI IQR SIR'[location] <> ""
    )
VAR _MBI =
    FILTER (
        SUMMARIZE (
            'CLABSI MBI SIR',
            'CLABSI MBI SIR'[summaryYM],
            'CLABSI MBI SIR'[location],
            'CLABSI MBI SIR'[infCount],
            'CLABSI MBI SIR'[numcldays]
        ),
        'CLABSI MBI SIR'[location] <> ""
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'CLABSI Metric FacType'[FacType] ),
        "Acute Care", _ACH,
        "Inpatient Rehab", _IRF
    )
(Return code truncated as this was my 5th attempt to get something to work)

[Fowmy]

@Kyle_Willey 

While creating a table or calculated column in Power BI, capturing slicer selections in real-time isn't feasible. Power BI constructs tables and columns before making them available on the report canvas. To assist you more effectively, kindly explain your requirements clearly, furnish sample data, and outline the expected outcome. This will enable a tailored solution to meet your specific needs. An approach using Measures should work in your case.

[Kyle_Willey]

@Fowmy, I am not sure how measures would solve my issue. For further explanation, I have several KPIs that I have written measures to handle, each of which switch with the risk analysis model selected by the slicers. However, in addition to the KPIs, I need to have a bar chart with the sum of infCount by month that also switches risk models (each on a different table) with the slicer. I also have a bar chart with the sum of infCount by location with the same requirement.

Each risk model is housed in it's own table. I can write a measure to do a simple sum/count/etc. The trouble is when I am trying to sum by month or by location AND have to specify which table to pull the data from with the slicer.