Forum Discussion

IF's avatar
IF
Post Prodigy
3 years ago
Solved

Data in different rows

Hi, I am using a data source that is not really designed in a good way. I have slicers for the month, type, and name. Score 3 is calculated by Score1/Score2. For example; Name=A, Month=7.2022 should...
Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    Hi IF ,

     

    I think the easiest fix is to also check the "#" in the slicer as shown in the image below:

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yadongf-msft's avatar
v-yadongf-msft
Community Support
3 years ago

Hi IF ,

 

Please create an index column in Power Query:

 

Please try following DAX to create new columns:

 

New score1 = 
var cur_index = 'Table'[Index]
var pre_index = 'Table'[Index] + 1
var pre_value = CALCULATE(
    MAX('Table'[Score1]),
    FILTER('Table','Table'[Index] = pre_index)
)
return 
IF('Table'[Score1] = BLANK(),pre_value,'Table'[Score1])


New score2 = 
var cur_index = 'Table'[Index]
var pre_index = 'Table'[Index] - 1
var pre_value = CALCULATE(
    MAX('Table'[Score2]),
    FILTER('Table','Table'[Index] = pre_index)
)
return 
IF('Table'[Score2] = BLANK(),pre_value,'Table'[Score2])

Score3 = 'Table'[New score2] &"/"& 'Table'[New score1]

 

 

You will get result you want:

 

 

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

IF's avatar
IF
Post Prodigy
3 years ago

Hi,

Thanks for the reply. I tried and noticed that I have some cases with more than 2 types. I added 3 more rows to the sample. For example, Name=B, date=8.2022 has initial (4), second (2) and last (3). In this case the score should be 4/50 for initial type, 2/50 for the second type and 3/50 for the last type. Most of the data has only one type, but some of them having 2 or 3 types. Thanks again. 

 

NameMonthTypeScore1Score2Score3
A7.2022initial 1Score1/Score2
A7.2022#24 Score1/Score2
A7.2022initial 2Score1/Score2
A8.2022second 2Score1/Score2
A8.2022#34 Score1/Score2
B7.2022initial 1Score1/Score2
B7.2022#43 Score1/Score2
B8.2022initial 4Score1/Score2
B8.2022second 2Score1/Score2
B8.2022last 3Score1/Score2
B8.2022#50 Score1/Score2

 

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    Hi IF ,

     

    Please try following DAX:

    score3 = 
var cur_score1 = CALCULATE(MAX('Table'[Score1]),FILTER('Table','Table'[Type] = "#" && 'Table'[Month] = EARLIER('Table'[Month]) && 'Table'[Name] = EARLIER('Table'[Name])))
VAR newscore1 = IF('Table'[Score1] = BLANK(),cur_score1)
RETURN IF(newscore1 <> BLANK(),'Table'[Score2] &"/" & newscore1)

     

    I think this is the result you want:

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • IF's avatar
      IF
      Post Prodigy
      3 years ago

      Thank you very much, it is very closed. I provided two screenshots. If i don't select the month and type the result should be 10/93 (1+4+3+2)/43+50. Similarly if I select the month it should be 9 / 50. Regards.