cancel
Showing results forย
Did you mean:ย
Post Patron

## Display Results for N and N-1 Step DashBord table results

hi all

i have one question about some condition to add to display N results and N-1 ( N her = Stage)

If for example on table dash board it's the results is for Stage = Stage1, Stage 2, Stage 3 and Stage 4 And it's asked to make to table results

Table 1 for STAGE

and

Table 2 Value of STAGE - 1

For example if we need Table Stage 4 results it will have Table 1 : STAGE 4 And Table 2 : STAGE 3 = STAGE -1 ( precedent one it's like Year and Year-1

Thanks for Help

You can find Power Bi  project her thanks for help

@MFelix

@amitchandak

1 ACCEPTED SOLUTION
Super User III

Hi @POWER_MI ,

The issue about the blank row is on the Calculation B2 on the table where you have the levels they are blanked out:

Having this will imply to change the calculation formula.

On the formula below I just show the B2 calculation where you have blanks so if on this specific line you have several R that is what is presented doesn't matter what you write in the column on the disconnected table.

``SELECTEDVALUE( 'Table'[Level] ) = "B2"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN  { "R2"; "R22"; "R31"; "R21" })``

If you don't want to present any R you then need to redo this part of the measure to:

``SELECTEDVALUE( 'Table'[Level] ) = "B2"; BLANK()``

With this the line will disappear.

Be aware that this solution is not dinamic so when you change the basis of the different levels , chaging R from one line to the other you need to update the Calculation measure accordingly.

Regarding the second question I'm not understanding.

You asked fopr one table to have the values from the slicer and the other for the slicer -1 on my tests the results where giving the correct result, maybe I have not understood what you want.

Can you please elaborate what is the exact error you are getting? Do you want to have on table the stage selected and on the second table the stage selected + previous stage?

Regards

Miguel Fรฉlix

Proud to be a Super User!

Check out my blog:

Power BI em Portuguรชs

18 REPLIES 18
Post Patron

Hi all,

@MFelix  is it clear for you ? in project stage i is replaced by 1...

project is here

thanks for help

Post Patron

Hi @MFelix

Thanks to answser

Before version or precedent version

Stage = SWITCH(TRUE(), COLOR_FIELD_TO_ADD[STEP] in {1,2,3}, Stage 1,
)
We change Stage 1 by Number 1
Stage 2 by Number 2
Stage 3 by Number 3
Stage 4 by Number 4
it's Not work ?
see project thanks
project is here

If for example

Stage is not caractere but number 1,2,3,4 how to find last step ?

Stage = SWITCH(TRUE(), COLOR_FIELD_TO_ADD[STEP] in {1,2,3}, 1,
)
see project thanks
project is here
Post Patron

Hi @MFelix

thanks for feed back.

If for example

Stage is not caractere but number 1,2,3,4 how to find last step ?

Stage = SWITCH(TRUE(), COLOR_FIELD_TO_ADD[STEP] in {1,2,3}, 1,
)
see project thanks
project is here

Super User III

Hi @POWER_MI ,

What is the purpose you need? the syntax seems fine. But what do you want to achieve?

Regards

Miguel Fรฉlix

Proud to be a Super User!

Check out my blog:

Power BI em Portuguรชs

Post Patron

Hi @MFelix

Thanks to answser

Before version or precedent version

Stage = SWITCH(TRUE(), COLOR_FIELD_TO_ADD[STEP] in {1,2,3}, Stage 1,
)
We change Stage 1 by Number 1
Stage 2 by Number 2
Stage 3 by Number 3
Stage 4 by Number 4
it's Not work ?
see project thanks
project is here

If for example

Stage is not caractere but number 1,2,3,4 how to find last step ?

Stage = SWITCH(TRUE(), COLOR_FIELD_TO_ADD[STEP] in {1,2,3}, 1,
)
see project thanks
project is here
Post Patron

Hi All,

Hello @MFelix

Have you seen last email. The project is updated with Stage i Like Number

Before version or precedent version

Stage = SWITCH(TRUE(), COLOR_FIELD_TO_ADD[STEP] in {1,2,3}, Stage 1,
)
We change Stage 1 by Number 1
Stage 2 by Number 2
Stage 3 by Number 3
Stage 4 by Number 4
it's Not work ?
see project thanks
project is here

If for example

Stage is not caractere but number 1,2,3,4 how to find last step ?

Stage = SWITCH(TRUE(), COLOR_FIELD_TO_ADD[STEP] in {1,2,3}, 1,
)
see project thanks
project is here
Thanks for help
Super User III

Hi @POWER_MI ,

Sorry for the late response add some work related issues.

Can you explain a little bit better your issue, looknig at the code you provide there should be no problem in having letters or numbers if things match in both sides should work colrretly.

Regards

Miguel Fรฉlix

Proud to be a Super User!

Check out my blog:

Power BI em Portuguรชs

Post Patron

Hi all,

@MFelix thanks for your feed back.

Post Patron

The update is :

First one that Table'Stage will be not exist So will used Stage colonne of COLOR_FIELD_TO_ADD table ( stage =1,2,3,4 and not stage 1; stage 2 ...

And also if we replace Stage 1, Stage 2 ... byt integer 1,2,3 it's not work.

Super User III

That has to do with the fact that the stage is coming from the same table has your filter so the previous stage is not being pickup on the values you need. You need to do it in a separated table.

Regards

Miguel Fรฉlix

Proud to be a Super User!

Check out my blog:

Power BI em Portuguรชs

Post Patron

Hi @MFelix

Thanks for your answer. You need to say that when we have for example on Table A, Step coloumn

so we can not define

Current Step Results and Precedent Step results ?

because on this updates is same example Stage will be not on other table but on data table with value and will be 1,2,3,4

thanks

Super User III

Hi @POWER_MI ,

This is the exact same thing has I explained in my previous post with the stage text, if you create a disconnected table to use has a slicer everything will work properly.

What is hapenning here is that you are using a slicer to filter a table then you are trying in the same data picking up a subset of you data in your case you are picking up all the stage 4 data. When you then with the same slicer try to get the subset of previous stage that information is no within the subset so no result. However due to context the total values in the matrix are getting pickup because it's based on a different context for the all table.

In this case if you use the same measures it will work correctly:

``````current stage Stage =
CALCULATE (
[Calculation];
FILTER (
)

previous stage Stage =
VAR previuos_selection =
SELECTEDVALUE ( Stage[Stage]) - 1

RETURN
CALCULATE (
[Calculation];
FILTER (
)
``````

Check PBIX file attach.

Regards

Miguel Fรฉlix

Proud to be a Super User!

Check out my blog:

Power BI em Portuguรชs

Super User IV

@POWER_MI , Create stage rank like this

new column

``````Stage Rank = SWITCH(TRUE(), COLOR_FIELD_TO_ADD[STEP] in {1,2,3}, 1,
)``````

``````this stage  = calculate( [measure], filter(all(COLOR_FIELD_TO_ADD), COLOR_FIELD_TO_ADD[Stage Rank] = max(COLOR_FIELD_TO_ADD[Stage Rank] )))

Try measure like

Proud to be a Super User!

Post Patron

Hi all,

The update is done on project but it's not working

seen enclosed projet

In this Stage there are [mesure]? which one ? on enclosed projet it's taken like Mesure1 count rows...

``this stage  = calculate( [measure], filter(all(COLOR_FIELD_TO_ADD), COLOR_FIELD_TO_ADD[Stage Rank] = max(COLOR_FIELD_TO_ADD[Stage Rank] )))``

You can find project here : donwload

Super User III

Hi @POWER_MI ,

Createa dimension table with the stages (disconnected) now add the following two measures:

``````current stage Stage =
CALCULATE (
[Calculation];
FILTER (
COLOR_FIELD_TO_ADD[Stage] = SELECTEDVALUE ( Stages[Stage] )
)
)

previous stage Stage =
VAR previuos_selection =
SELECTEDVALUE ( Stages[Id] ) - 1
VAR Previouse_stage =
MAXX (
FILTER ( ALL ( Stages ); Stages[Id] = previuos_selection );
Stages[Stage]
)
RETURN
CALCULATE (
[Calculation];
FILTER (
)
)``````

On the attach file the top two tables Ihave forced the filter on the filter pane just for comparision the bottom ones are working with the measures.

The formating you can use the same condittional formatting for both measure has you use the previous one.

Regards

Miguel Fรฉlix

Proud to be a Super User!

Check out my blog:

Power BI em Portuguรชs

Post Patron

hi All,

@MFelix Thanks for solution

there are two remarks

for Update 1 : on the update one you will see that when the table "table have for example one line with empty value on dashboard results the line will not have colonne 1 and there will be results with choice B green color or correct results will be just two line and No line with green color on exmaple " Type = B"

on table data base Line B, B2 is Empty so logicly the results will be also.

update 2 for the stage N and Stage N-1

for example example when you will are on Stage 4 the table displayed will be

Dashboard results group1

- Stage 4

- Stage 3

the mistakes for example when you do

dashboard results Stage 3

Stage 3

Stage 2

the results on Group 1 and 2 are not same for same stage ?

also the count Ri is not correct ?

thanks for help

Super User III

Hi @POWER_MI ,

The issue about the blank row is on the Calculation B2 on the table where you have the levels they are blanked out:

Having this will imply to change the calculation formula.

On the formula below I just show the B2 calculation where you have blanks so if on this specific line you have several R that is what is presented doesn't matter what you write in the column on the disconnected table.

``SELECTEDVALUE( 'Table'[Level] ) = "B2"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN  { "R2"; "R22"; "R31"; "R21" })``

If you don't want to present any R you then need to redo this part of the measure to:

``SELECTEDVALUE( 'Table'[Level] ) = "B2"; BLANK()``

With this the line will disappear.

Be aware that this solution is not dinamic so when you change the basis of the different levels , chaging R from one line to the other you need to update the Calculation measure accordingly.

Regarding the second question I'm not understanding.

You asked fopr one table to have the values from the slicer and the other for the slicer -1 on my tests the results where giving the correct result, maybe I have not understood what you want.

Can you please elaborate what is the exact error you are getting? Do you want to have on table the stage selected and on the second table the stage selected + previous stage?

Regards

Miguel Fรฉlix

Proud to be a Super User!

Check out my blog:

Power BI em Portuguรชs

Post Patron

Hi @MFelix

thanks

it's work fine

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!