Forum Discussion

JohnD's avatar
JohnD
Regular Visitor
8 years ago
Solved

Line chart - Show only lines with zeros

I have a filtered line chart that shows a count (Y-axis) over time (workday on X-axis) for various devices.

 

I want to only show the lines that have a zero value at some point during this particular time period. I tried 'Advanced Filtering > Show items when the value is 0' but it only shows the individual zero values, not the data series the zero belongs to.

 

In the following example, only 2 lines should show. Is there a way to do this?

 

Thanks.

 

 

 

  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi JohnD,

    Got it. For your scenario we can't get the table Power BI generated during creating the visual dynamically. We can only export the data to a CSV file, it's not dynamic. Furthermore, we also can't create a dynamic table based on slicer or filter, please review this knowledage base.

    Best Regards,
    Angelia

9 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi JohnD，

    How do you draw the line chart? You add a column in the Legend field or you add multiple column(measure) in the Value field? If you add multiple columns in value field, you can just only the two needed line which have a zero value based on my understanding, please share more details for further analysis.

    Best Regards,
    Angelia

    • JohnD's avatar
      JohnD
      Regular Visitor
      8 years ago

      Hi Angelia,

       

      There is only one column used fo the Legend ([DeviceNumber]) and one measure for the Values.

      The measure is: ReplaceByZero = CALCULATE(COUNTROWS(Transactions),Transactions[TransactionID]) + 0

      Its sole purpose is to replace null values by 0 to force the line chart to accurately show when there was 0 transaction for a particular device.

      There is a one-to-many relationship between the Devices table and the Transactions table.

      When I add the DeviceNumber column as Legend, Power BI breaks down the total Transactions count per Device, which allows me, as you mentionned, to filter per device and only show the ones I want.  There are a lot of devices however and I'm only interested in the ones with 0 values. How can I automatically filter all the other devices out?

      The larger question would be: How can I access the table dynamically generated by Power BI and how do I delete a column if it doesn't contain a certain value (0 in my case)?

       

      Thanks.

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi JohnD,

        Got it. For your scenario we can't get the table Power BI generated during creating the visual dynamically. We can only export the data to a CSV file, it's not dynamic. Furthermore, we also can't create a dynamic table based on slicer or filter, please review this knowledage base.

        Best Regards,
        Angelia