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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
smorph
Frequent Visitor

Issue with correct output

Hey everyone,

I use a Forms survey which I like to "analyze" with Power Bi.
I already found an option to be able to compare the answers together.

 

Goal from my side is to reccomend the next steps to end user of that report about the next steps which they should go.

 

visuals.PNG

As you can see, I'm using three visuals with a dedicated colum to calculate the sum of all given answers to a specific topic, in this case: daily business, change and project.

In the visual below should be getting up the recommendation.
In the specific case you see now there should be shown up "daily business", but instead i get "change". Please find the code below.

 

 

 

The colums I'm using are: 1 Tagesgeschäft  (daily business) (left visual); 2 Change (middle visual); 3 Projekt (right visual).

 

    SWITCH(
        TRUE(),
        //Prüfung ob Tagesgeschäft
        'Projektwürdigkeitsanalyse'[1 Tagesgeschäft] > 'Projektwürdigkeitsanalyse'[2 Change] && 'Projektwürdigkeitsanalyse'[1 Tagesgeschäft] > 'Projektwürdigkeitsanalyse'[3 Projekt], "Tagesgeschäft",

        //Prüfung ob Chaage
        'Projektwürdigkeitsanalyse'[2 Change] > 'Projektwürdigkeitsanalyse'[1 Tagesgeschäft] && 'Projektwürdigkeitsanalyse'[2 Change] > 'Projektwürdigkeitsanalyse'[3 Projekt], "Change",

        //Prüfung ob Projekt
        'Projektwürdigkeitsanalyse'[3 Projekt] > 'Projektwürdigkeitsanalyse'[1 Tagesgeschäft] && 'Projektwürdigkeitsanalyse'[3 Projekt] > 'Projektwürdigkeitsanalyse'[2 Change], "Projekt",

        //Ausgabe wenn nicht zutreffend
        "nicht eindeutig")

 

 

What I try to achive is that, if the column "1 Tagesgeschäft" is bigger than the other two then the below visual should return "Tagesgeschäft", but instead I get "Change" returned.

 

I already tried to put SUM() around each column, with no effect.

 

I already checked with other entries and when only one of the three colums is bigger than 0 and the other ones are exactly 0 it works fine.

Right now I'm not able to find the issue...

Please the code of the three columns:

 

1 Tagesgeschäft = 
    IF('Projektwürdigkeitsanalyse'[Anzahl involvierter Bereiche] = "= 1", 3, 0) +
    IF('Projektwürdigkeitsanalyse'[Anzahl involvierter Personen] = "<= 3", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Voraussichtliche Dauer] = "wiederkehrend", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Voraussichtliche Investitionshöhe] ="<= 10.000 €", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Inhaltliche Komplexität] = "gering", 3, 0) +
    IF('Projektwürdigkeitsanalyse'[Neuartigkeit] = "gering", 2, 0) +
    IF('Projektwürdigkeitsanalyse'[Risiken] = "gering", 1, 0)

2 Change = 
    IF('Projektwürdigkeitsanalyse'[Anzahl involvierter Bereiche] = "<= 3", 3, 0) +
    IF('Projektwürdigkeitsanalyse'[Anzahl involvierter Personen] = "<= 5", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Voraussichtliche Dauer] = "<= 3 Monate", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Voraussichtliche Investitionshöhe] ="< 50.000 €", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Inhaltliche Komplexität] = "mittel", 3, 0) +
    IF('Projektwürdigkeitsanalyse'[Neuartigkeit] = "mittel", 2, 0) +
    IF('Projektwürdigkeitsanalyse'[Risiken] = "mittel", 1, 0)

3 Projekt = 
    IF('Projektwürdigkeitsanalyse'[Anzahl involvierter Bereiche] = "> 3", 3, 0) + 
    IF('Projektwürdigkeitsanalyse'[Anzahl involvierter Personen] = "> 5", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Voraussichtliche Dauer] = "> 3 Monate", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Voraussichtliche Investitionshöhe] =">= 50.000 €", 1, 0) +
    IF('Projektwürdigkeitsanalyse'[Inhaltliche Komplexität] = "hoch", 3, 0) +
    IF('Projektwürdigkeitsanalyse'[Neuartigkeit] = "hoch", 2, 0) +
    IF('Projektwürdigkeitsanalyse'[Risiken] = "hoch", 1, 0)

 

 

Thanks in advance.

2 REPLIES 2
smorph
Frequent Visitor

Hey @v-yadongf-msft,

thanks for getting back to me.

 

  1. This one is the data from the forms excelfile which will be pushed into datase.
    data.png
  2. This one is the expected result built in an other excel file (that was the basic idea)
    expected result.png

 

 

 

 

 

 

 

The 2nd pic shows you the initial idea of the evaluation which is now built with forms and the report.

Whats happens there:

 

By choosing one of the three options for each row (last colum), there will be created a total sum for each of the three columns (colums: 3, 6 and 9). Where the red box is, the sums will be compared and the biggest one will give back one of the three options: "Tagesgeschäft (daily business)", "Change" or "Project".

On report level I'm following the same idea by creating a sum for each of the answers (my last code block from initial post), that works very well and is double checked with that excel file.

My idea is to get the red box (screenshot) into a visual (first screen, 4th visual, initial post), by comparing the sums, like in the excel. For only one answer that will work very well, if I got more than one, its a total mess.

 

What I already found out is that the comparision of the sums works fine on each answer themselves but not in total over all answers.

 

Please let me know if you need something else.

Best regards,
Steffen

v-yadongf-msft
Community Support
Community Support

Hi @smorph ,

 

In order to better understanding your demands and give the right solution, could you please share with me some screenshots of your data after hiding sensitive information and tell me what's your expected output?

 

Thanks for your efforts & time in advance.

 

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors