Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Get data from table 2

Hi Guys,

I hope you can help me. I'm new to the Power BI.

 

Table 1: Data from Mar 19 - till date

Table 2: Data from Jan 19 - Feb 19

Table 3: Calendar table

 

I would like to get the data from table 2 as well when I select month name from Calendar table. I've created relationship but it doesn't work out for table 2. Please help me.

 

 

Kind regards,

pv

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

    Your column chart's x-axis and y-axis are both from Table 1, not Table 2. So, it will show blank when you choose "January" or "February". 

    Try this:

    1. Create measures. Please pay attention to your blank values in "Concatenate". Not blank, but "   ".

     

    Count of Defected data =
IF (
    MAX ( 'Data Collection'[Concatenate] ) = "   ",
    BLANK (),
    COUNT ( 'Data Collection'[Concatenate] )
)
    Count of defected =
IF (
    MAX ( 'dCollection(Jan-Feb)'[Concatenate] ) <> "   ",
    COUNT ( 'dCollection(Jan-Feb)'[Concatenate] )
)
    Defected all =
IF (
    ISBLANK ( MAX ( 'Concatenate'[Concatenate] ) ),
    BLANK (),
    [Count of defected] + [Count of Defected data]
)

     

    2. Create a table.

     

    Concatenate =
FILTER (
    VALUES ( 'Data Collection'[Concatenate] ),
    'Data Collection'[Concatenate] <> "   "
)

     

    3. Create a column chart.

     

    PBIX file attached.

     

    Best Regards,
    Icey

     

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

18 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Dear Anonymous 

     

    I'm trying to understand that from the table 2,

    what columns/measures do you want to display in the report 

    If using single measures between table 1 and table2 then create the calculated measures from 2 tables

     

    Regards, 

     

    Munna

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Dear vijaykolisetty,

      Thank you so much for the reply. 

      I've created measures. I hope you can understand after see the below print screen. I don't understand one more thing here, I created relationship with date column for three tables and if select column from one table then automatically show linked table (highlighted with tick mark for table in the left corner) but it doesn't show that.

       

      I'm thinking my table relationship is wrong. Is that right?

       

      Kind regards,

      pv

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Dear Anonymous 

        Thank you for the screenshot, in this case there are 2 possibilities 

        1. To check the relationships or change the relationship type into bi-directional

        2. Check the data type of the date or change the date column into date format and test the data.

         

        Regards, 

        Munna

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,
    I am not sure about the solution but i have a workaround you can try as of now.

    Create a measure:

    Measure = IF(
    SELECTEDVALUE('Date'[Date]) = SELECTEDVALUE('Table'[Date]) ||
    SELECTEDVALUE('Date'[Date]) = SELECTEDVALUE('Table (2)'[Date]),
    1,
    0
    )
     
    Put this in table visual and also on visual level filter, select value 1 and apply.
     
    Thanks,
    Vinay Dandwani
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Vinay,

       

      Thank you so much for the reply.

       

      I think your formula is correct but I'm not getting, it says can't display. Sorry, I think I'm doing something wrong.

       

      Kind regards,

      pv

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

    Your column chart's x-axis and y-axis are both from Table 1, not Table 2. So, it will show blank when you choose "January" or "February". 

    Try this:

    1. Create measures. Please pay attention to your blank values in "Concatenate". Not blank, but "   ".

     

    Count of Defected data =
IF (
    MAX ( 'Data Collection'[Concatenate] ) = "   ",
    BLANK (),
    COUNT ( 'Data Collection'[Concatenate] )
)
    Count of defected =
IF (
    MAX ( 'dCollection(Jan-Feb)'[Concatenate] ) <> "   ",
    COUNT ( 'dCollection(Jan-Feb)'[Concatenate] )
)
    Defected all =
IF (
    ISBLANK ( MAX ( 'Concatenate'[Concatenate] ) ),
    BLANK (),
    [Count of defected] + [Count of Defected data]
)

     

    2. Create a table.

     

    Concatenate =
FILTER (
    VALUES ( 'Data Collection'[Concatenate] ),
    'Data Collection'[Concatenate] <> "   "
)

     

    3. Create a column chart.

     

    PBIX file attached.

     

    Best Regards,
    Icey

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Icey,

       

      Thanks for the solution. 

       

      If I select by line 100 then it should get around 1667 but it showing too many like ten thousands in bar charts.

       

      And also once I select by line 100 then both charts should get same values not different.

       

      Could you please recheck why its showing. Sorry to trouble you.

       

      Kind regards,

      pv

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Icey,

         

        Sorry. Its not 1667 because which you got old data and I did refresh the data file now, so it won't come 1667 for you but both charts value should get same.

        Kind regards,

        pv

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

    Sorry to reply late.

    You can create your measures in any table. However, the new table "Concatenat" which is a slicer table need to be independent of other tables. If it is not independent of other tables, it will be affected by them when selected. The effect is called interaction.

     

    Best Regards
    Icey

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Ok thank you Icey.

      Kind regards,

      pv

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Icey,

      Thank you. I couldn't reply from private message.

      I've tried with sample to get values from different table but it doesn't work out from my side. Please see the below print screen. I think something wrong with my Power BI.

      I've tried to create second column in same file (test832609) which you sent me the link but doesn't work out. 

       

      Kind regards,

      pv

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi Anonymous ,

        You need to create a Table.

         

        Best Regards,

        Icey