Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
POWER_MI
Post Patron
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

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:

MFelix_0-1605799917884.png

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

18 REPLIES 18
POWER_MI
Post Patron
Post Patron

Hi all,

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

project is here 

thanks for help

POWER_MI
Post Patron
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,
COLOR_FIELD_TO_ADD[STEP] in {4}, Stage  2,
COLOR_FIELD_TO_ADD[STEP] in {5}, Stage 3,
COLOR_FIELD_TO_ADD[STEP] in {6,7},Stage 4
)
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,
COLOR_FIELD_TO_ADD[STEP] in {4}, 2,
COLOR_FIELD_TO_ADD[STEP] in {5}, 3,
COLOR_FIELD_TO_ADD[STEP] in {6,7}, 4
)
see project thanks
project is here 
POWER_MI
Post Patron
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,
COLOR_FIELD_TO_ADD[STEP] in {4}, 2,
COLOR_FIELD_TO_ADD[STEP] in {5}, 3,
COLOR_FIELD_TO_ADD[STEP] in {6,7}, 4
)
see project thanks
project is here 
 

Hi @POWER_MI ,

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



POWER_MI_0-1606832117881.png

 

Hi @MFelix 

Thanks to answser

Before version or precedent version 

Stage = SWITCH(TRUE(), COLOR_FIELD_TO_ADD[STEP] in {1,2,3}, Stage 1,
COLOR_FIELD_TO_ADD[STEP] in {4}, Stage  2,
COLOR_FIELD_TO_ADD[STEP] in {5}, Stage 3,
COLOR_FIELD_TO_ADD[STEP] in {6,7},Stage 4
)
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,
COLOR_FIELD_TO_ADD[STEP] in {4}, 2,
COLOR_FIELD_TO_ADD[STEP] in {5}, 3,
COLOR_FIELD_TO_ADD[STEP] in {6,7}, 4
)
see project thanks
project is here 

Hi All, 

Hello @MFelix

@amitchandak 

  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,
COLOR_FIELD_TO_ADD[STEP] in {4}, Stage  2,
COLOR_FIELD_TO_ADD[STEP] in {5}, Stage 3,
COLOR_FIELD_TO_ADD[STEP] in {6,7},Stage 4
)
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,
COLOR_FIELD_TO_ADD[STEP] in {4}, 2,
COLOR_FIELD_TO_ADD[STEP] in {5}, 3,
COLOR_FIELD_TO_ADD[STEP] in {6,7}, 4
)
see project thanks
project is here 
Thanks for help

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi all, 

@MFelix thanks for your feed back. 

New Project is here link

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.

Hi @POWER_MI

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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 (
        ALL ( COLOR_FIELD_TO_ADD[Stage] );
        COLOR_FIELD_TO_ADD[Stage] = SELECTEDVALUE(Stage[Stage]))
    )



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

RETURN 
    CALCULATE (
        [Calculation];
        FILTER (
            ALL ( COLOR_FIELD_TO_ADD[Stage] );
            COLOR_FIELD_TO_ADD[Stage] = previuos_selection)
        )
    

 

Check PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



amitchandak
Super User
Super User

@POWER_MI , Create stage rank like this

new column

Stage Rank = SWITCH(TRUE(), COLOR_FIELD_TO_ADD[STEP] in {1,2,3}, 1,
COLOR_FIELD_TO_ADD[STEP] in {4}, 2,
COLOR_FIELD_TO_ADD[STEP] in {5}, 3,
COLOR_FIELD_TO_ADD[STEP] in {6,7}, 4
)

 

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

last stage  = calculate( [measure], filter(all(COLOR_FIELD_TO_ADD), COLOR_FIELD_TO_ADD[Stage Rank] = max(COLOR_FIELD_TO_ADD[Stage Rank] )-1))

 

Try measure like

Hi all,

@amitchandak thanks for your answer 

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

Hi @POWER_MI ,

 

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

 

current stage Stage =
CALCULATE (
    [Calculation];
    FILTER (
        ALL ( COLOR_FIELD_TO_ADD[Stage] );
        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 (
            ALL ( COLOR_FIELD_TO_ADD[Stage] );
            COLOR_FIELD_TO_ADD[Stage] = Previouse_stage
        )
    )

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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 ?

Enclosed the projet PB Donwload

thanks for help 

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:

MFelix_0-1605799917884.png

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix 

thanks 

it's work fine

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.