Need some suggestions to start

DAX DAX Calculations
1

Hi everyone,

I have this simple report to build but I don’t have a clue on how to do it. I’m new to Power BI and would like some insights and ideas.

Thanks in advance.

We
Sample file.xlsx (11.8 KB)
ndy

2

@WendyZhang Since you didnt’ specify any logic, I have tried to match what you need based on my own understanding.

Sample file.xlsx (19.8 KB)

let
    Source         = Excel.CurrentWorkbook(){[ Name = "Table1" ]}[Content],
    AddedCustom    = Table.AddColumn (
        Source,
        "Custom",
        ( OuterTable ) =>
            let
                SelfJoin            = Table.SelectRows (
                    Source,
                    ( InnerTable ) => InnerTable[Dim_Insured] = OuterTable[Dim_Insured]
                ),
                GetPolicyTypeAsList = SelfJoin[#"Dim_Policy[Policy_Type]"],
                ContainsAllWords    = List.ContainsAll (
                    GetPolicyTypeAsList,
                    { "Auto", "Property" }
                )
            in
                ContainsAllWords
    ),
    KeepTrue       = Table.SelectRows ( AddedCustom, each ( [Custom] = true ) ),
    RemovedColumns = Table.RemoveColumns ( KeepTrue, { "Custom" } ),
    PivotedColumn  = Table.Pivot (
        RemovedColumns,
        List.Distinct ( RemovedColumns[#"Dim_Policy[Policy_Type]"] ),
        "Dim_Policy[Policy_Type]",
        "Dim_Policy[POLICY_NO]"
    )
in
    PivotedColumn
3

Thank you so much Antriksh for the prompt response.

My bad, I didn’t mention it has to be done with the Tablular model built within the company. I manged to get the table using DAX but now facing difficulty removing the rows with blank field as shown in the new attachment. Would you be able to help on that?

Attached is the updated file with DAX used to get the results.
Sample file.xlsx (12.6 KB)

Thanks again for your help.

Wendy

4

You can use a FILTER function over the table to check Property <> “”.

5

Yes, I’ve tried the filter function but unsuccessful. Maybe I didn’t get the syntax corrctly. I’ll try different ways again.

Appreciate your help very much.

Wendy

6

@WendyZhang did the response provided help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

7

A quick update. I was able to remove the blank rows using the filter. Thank you Antriksh for the alternative solution. Learned something new from you.

Thank you,

Wendy