CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
nsr
nsr

Reputation: 107

Power BI - Dynamic Table with Measure

I have a Dataset like this

patient_name age gender test_result
Alex 48 M Positive
Joe 35 M Negative
Divya 45 F Positive

And in my power BI Dashboard i need to display a free form table like below

Item Description Value
Total number of Adult-Male Patients with Positive Test Result 1252
Total number of Adult-Female Patients with Positive Test Result 856
Percent of Positive 2.8

I have the measures created for calculations. I tried to create a custom table with measures, but they are not changing dynamically. The table is showing only static values, when slicer selected value changes. Is there a better way to present this ?

Thank you, NSR

Upvotes: 0

Views: 2107

Answers (2)

Umut K
Umut K

Reputation: 1388

first create an empty table

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Description" = _t, Value = _t])
in
    Source

then we will fill this table with your measures by creating a table...

Modelling --> New Table

    Report Table =
UNION (
    Report,
    ROW (
        "Item Description", "Total number of Adult-Male Patients with Positive Test Result",
        "Value",
            CALCULATE (
                COUNT ( 'Table'[test_result] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[test_result] = "positive"
                        && 'Table'[gender ] = "m"
                        && 'Table'[age ] > 18
                )
            )
    ),
    ROW (
        "Item Description", "Total number of Female-Male Patients with Positive Test Result",
        "Value",
            CALCULATE (
                COUNT ( 'Table'[test_result] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[test_result] = "positive"
                        && 'Table'[gender ] = "f"
                        && 'Table'[age ] > 18
                )
            )
    ),
    ROW (
        "Item Description", "Percentage Positive",
        "Value",
            FORMAT (
                DIVIDE (
                    CALCULATE (
                        COUNT ( 'Table'[test_result] ),
                        FILTER (
                            ALLSELECTED ( 'Table' ),
                            'Table'[test_result] = "positive"
                                && 'Table'[age ] > 18
                        )
                    ),
                    CALCULATE ( COUNT ( 'Table'[test_result] ), ALL ( 'Table' ) )
                ),
                "Percent"
            )
    )
)

result

Upvotes: 0

Umut K
Umut K

Reputation: 1388

did you try ALLSELECTED in your measures?

Total number of Adult-Male Patients with Positive Test Result =
CALCULATE (
    COUNT ( 'Table'[test_result] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[test_result] = "positive"
            && 'Table'[gender ] = "m"
            && 'Table'[age ] > 18
    )
)

Upvotes: 0

Related Questions