Forum Discussion

ankurshah01's avatar
ankurshah01
Frequent Visitor
2 years ago
Solved

Get Month Value in new column (another table) where Rank = 1

Hi All, 

 

I have a table that shows below data. 

Table A

CalmonthRank1Volume
Jan-23440
Feb-23150
Mar-23245
Apr-23634
May-23920
Jun-23825
Jul-23536
Aug-23730
Sep-231010
Oct-23118
Nov-23342

Rank1 is a measure.

Now based on above table, i need to get the Calmonth value where Rank1 = 1 in Table B. 

So in this case i will get Feb 2023. 

 

I created a new column in Table B & tried using Calculate with max & filter (where rank = 1) but it returns always Nov 2023. 

New Column = CALCULATE(max('Table A'[Calmonth]),FILTER('Table A', 'Table A'[Rank1] = 1))

I tried with different options using Lookup or selectedvalue but not getting required output. 

 

how to resolve this issue? 

 

Regards

Ankur

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ankurshah01 ,

    Current you can't create dynamic calculated column or table based on filter effect, they works on different levels and you can't use the child level to effect its parent.

    You can try to use the following measure formula if helps:

    formula =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Table1 ),
        [Calmonth],
        "Rank", [Rank1],
        "CalMonth",
            CALCULATE (
                SUM ( table2[Volume] ),
                FILTER ( ALLSELECTED ( Table2 ), Table2[Calmonth] = [Calmonth] )
            )
    )
RETURN
    MAXX ( FILTER ( summary, [Rank] = 1 ), [CalMonth] )

    Notice: the data level of power bi(from parent to child level)

    Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ankurshah01 ,

    Current you can't create dynamic calculated column or table based on filter effect, they works on different levels and you can't use the child level to effect its parent.

    You can try to use the following measure formula if helps:

    formula =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Table1 ),
        [Calmonth],
        "Rank", [Rank1],
        "CalMonth",
            CALCULATE (
                SUM ( table2[Volume] ),
                FILTER ( ALLSELECTED ( Table2 ), Table2[Calmonth] = [Calmonth] )
            )
    )
RETURN
    MAXX ( FILTER ( summary, [Rank] = 1 ), [CalMonth] )

    Notice: the data level of power bi(from parent to child level)

    Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

    Regards,

    Xiaoxin Sheng